CODEWITHSUNDEEP
vbaexcel
irozak
irozak

Reputation: 128

Insert row if not already there

I need to insert a separator row between the new data, and ignore the existing data above it which has already been separated.

This code does that but keeps adding rows to empty rows every time it is run.

Sub InsertRows()
  Dim lngLstRow As Long

  lngLstRow = Range("u" & Rows.Count).End(xlUp).Row
  For i = lngLstRow To 11 Step -1
    If Not IsEmpty(Range("u" & i)) Then
      If Range("u" & i) <> Range("u" & i - 1) Then
        Range("u" & i).EntireRow.Insert
      End If
    End If
  Next
End Sub

Upvotes: 1

Views: 47

Answers (1)

YowE3K
YowE3K

Reputation: 23994

You need to change your code to not insert the extra row if this row, or the previous row, is empty:

Sub InsertRows()
  Dim lngLstRow As Long
  Dim i As Long

  lngLstRow = Range("u" & Rows.Count).End(xlUp).Row
  For i = lngLstRow To 11 Step -1
    If Not (IsEmpty(Range("u" & i)) Or IsEmpty(Range("u" & i - 1))) Then
      If Range("u" & i) <> Range("u" & i - 1) Then
        Range("u" & i).EntireRow.Insert
      End If
    End If
  Next
End Sub

Upvotes: 1

Related Questions