Macro code to test for blank row and insert blank row if false

[Guest]

Below is the code I am using to insert a blank row between text pasted to
Excel at various times. However, I would like to add a test if a blank row
exists between rows of text, then do not insert another blank row. Here's
what I currently have:

Sub InsertRows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 2 Step -1
Rows(RowNdx).Insert
Next RowNdx

End Sub

Any ideas?

 

[John]

Hi Mattie,

Try this:

Sub InsertRows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 2 Step -1
If Cells(RowNdx - 1, 1).Value <> "" Then
Rows(RowNdx).Insert
Else
RowNdx = RowNdx - 1
End If
Next RowNdx

End Sub

Note that it only tests for a blank value in the respective cell of column
A.

Hope that helps

Best regards

John

 

[Guest]

Perfect! Thank you.

Mattie

Hi Mattie,

Try this:

Sub InsertRows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 2 Step -1
If Cells(RowNdx - 1, 1).Value <> "" Then
Rows(RowNdx).Insert
Else
RowNdx = RowNdx - 1
End If
Next RowNdx

End Sub

Note that it only tests for a blank value in the respective cell of column
A.

Hope that helps

Best regards

John