CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

If cell in range is not empty insert new row below

I have numbers and letters in range B2:B60.

I would like, if there is a data in cells B2:B60, to insert one row below.

So it would look like:

   A B
1    e -> EntireRow.Insert below
2    
3    3 -> EntireRow.Insert below
4    a -> EntireRow.Insert below
5    
6    er -> EntireRow.Insert below
7    
8    w -> EntireRow.Insert below

I have tried:

Dim Rng As Range
For Each Rng In Range("B2:B60")
    If Is Not Empty (Rng.Value) Then
        Rng.Offset(1, 0).EntireRow.Insert
    End If
Next

Upvotes: 0

Views: 1129

Answers (2)

NickSlash
NickSlash

Reputation: 5077

You might need to reverse your loop to prevent your data moving out of the input range during execution when you insert new rows. Not sure how For Each loops handle things like that.

Sub Main()

Dim R As Range
Set R = Range("Sheet1!C4:C20")
Dim FR As Integer
Dim LR As Integer

FR = 1 ' First Row in R
LR = R.Rows.Count ' Last Row in R

Dim Index As Integer

For Index = LR To FR Step -1
    If Not IsEmpty(R(Index)) Then
        R(Index).Offset(1, 0).EntireRow.Insert
    End If
Next

End Sub

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54777

If Not IsEmpty

Option Explicit

Sub EmptyRows()

    Dim Rng As Range

    For Each Rng In Range("B2:B60")
        If Not IsEmpty(Rng) Then
            Rng.Offset(1, 0).EntireRow.Insert
        End If
    Next

End Sub

Upvotes: 1

Related Questions