how can i insert blank row on above and below of selected rows

[nomans2325]

Hi guys,

I just want to know that how can I insert new row on above and below of selected range of rows using vba
thanks in advance for your response

BR,NS

 

[Zot]

Say your selected range is A10
To add a row above:
Range("A10").Offset(-1 , 0).EntireRow.Insert

To add a row below:
Range("A10").Offset(1 , 0).EntireRow.Insert

 

[nomans2325]

Say your selected range is A10 To add a row above: Range("A10").Offset(-1 , 0).EntireRow.Insert To add a row below: Range("A10").Offset(1 , 0).EntireRow.Insert

 

[nomans2325]

thanks for you reply but if i selected multiple rows than how to achieve this let say user select row 5 to 8 than how to add blank row at above and below in addition user can select row as much as he want

 

[Zot]

thanks for you reply but if i selected multiple rows than how to achieve this let say user select row 5 to 8 than how to add blank row at above and below in addition user can select row as much as he want

You meant adding rows above and below on each row 5, row 6, row 7 and row 8 for a total of 8 additional rows?

 

[Logit]

.
Try this :

Option Explicit

Sub InsertRowAboveNBelow()

Dim ws As Worksheet
Dim mySel As Range
Dim lRowSel As Long
Dim mySelVis As Range
Dim lAreas As Long
Dim lRowsArea As Long

Set ws = ActiveSheet
Set mySel = Selection.EntireRow
Set mySelVis = mySel.SpecialCells(xlCellTypeVisible)

lAreas = Selection.Areas.Count
lRowsArea = Selection.Areas(lAreas).Rows.Count

Selection.Resize(rowsize:=1).Rows(1).EntireRow. _
     Resize(rowsize:=1).Insert Shift:=xlDown

lRowSel = mySel.Areas(lAreas).Cells(lRowsArea, 1).Row + 1
ws.Cells(lRowSel, 1).EntireRow.Insert

Range("A1").Select
End Sub

 

[footoo]

Maybe :

Sub v()
If Selection.Areas.Count <> 1 Then Exit Sub
Selection(Selection.Rows.Count + 1).EntireRow.Insert
Selection(1).EntireRow.Insert
End Sub

 

[Zot]

I was wrong here. If you want to insert row above the selected range, no need to offset it. but just Insert.EntireRow

 

[Zot]

If you select either contiguous or non-contiguous range and you want to add a row above and below for each selected range, you can use this code:

Sub AddRows()

Dim i As Long, nCount As Long
Dim vAdd As Variant
Dim strAdd As String, ArryAdd() As String
Dim cell As Range, rng As Range

Set rng = Selection
If Selection.Count = 0 Then Exit Sub

For Each cell In rng
    strAdd = strAdd & cell.Address & " "
Next
ArryAdd = Split(strAdd)
nCount = 0
For i = 0 To UBound(ArryAdd) - 1
    With Range(ArryAdd(i)).Offset(nCount, 0)
        .EntireRow.Insert
        .Offset(1, 0).EntireRow.Insert
    End With
    nCount = nCount + 2
Next

End Sub