add blank row in an if statement

  • Thread starter Thread starter BBoyd
  • Start date Start date
B

BBoyd

i am trying to add a blank row after i compare 2 #s in a column. i am using
the if statement to compare and if they are not = then i want to add a blank
row in between them. any suggestions? i am having a real time trying to get
this to work out.

thank you
 
Before/after example. This is usually done with a for/each loop from the
bottom up.
 
Dim LastRow As Long
Dim i As Long

With Activesheet

LastRow = .Cells(i, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value <> .Cells(i-1, "A").Value Then

.Rows(i).Insert
Ed If
Next i
End With

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You could use this udf

Sub InsertRows()
Dim cRows, i, WhichColumn As Long
Dim TempColumn As String

TempColumn = InputBox("Which column do you want to compare?")

'Can input either the column label,or the number of the column
If Application.WorksheetFunction.IsNumber(TempColumn) Then
WhichColumn = TempColumn
ElseIf Application.WorksheetFunction.IsText(TempColumn) Then
WhichColumn = Asc(StrConv(TempColumn, 1)) - 64
End If

'Get a count of the rows
cRows = Cells(Rows.Count, WhichColumn).End(xlUp).row

' Walks backwards to avoid the row numbers changing
For i = cRows To 3 Step -1
If Cells(i, WhichColumn).value <> Cells(i - 1, WhichColumn).value Then
Cells(i, WhichColumn).EntireRow.Insert
End If
Next
End Sub
 
Back
Top