add blank row in an if statement

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
 
D

Don Guillett

Before/after example. This is usually done with a for/each loop from the
bottom up.
 
B

Bob Phillips

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)
 
M

mikebres

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
 
Top