VBA IF function: when two cells are different [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I've been looking unsuccesfully everywhere for an answer to how to create an IF loop for the following situation:

I have an Excel sheet with different words eg. names, going all the way down column A.
I want to create a loop where if two cells have different names, then 2 blank rows gets inserted between them.

eg.
Cell A1: Anne
Cell A2: Anne
Cell A3: Bob
Cell A4: Charlie

So between A1 & A2 two blanks rows will be inserted. Between Bob & Charlie another 2 blank rows gets inserted.

Any help much appreciated!
Thanks in advance

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
try this macro


Sub test()
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
Dim j As Integer, k As Integer
j = Range("A1").End(xlDown).Row
'j is the last row
For k = j To 2 Step -1
If Cells(k, 1) <> Cells(k - 1, 1) Then
Range(Cells(k, 1), Cells(k + 1, 1)).EntireRow.Insert
End If
Next k
End Sub
Thanks a lot of for that. For some reson it adds extra rows at the top if my data starts of row 10 instead of A1- is there any way to stop this?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
Try this slightly modified macro
when you invoke th macro
An input box will come up. fill in the initial cell address for e.g A10.

Sub test()
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
Dim j As Integer, k As Integer, m As Integer, r As String
 r = InputBox("type the first cell under reference e.g. A10")
m = Range(r).Row
j = Range("A10").End(xlDown).Row
'j is the last row
For k = j To m + 1 Step -1
If Cells(k, 1) <> Cells(k - 1, 1) Then
Range(Cells(k, 1), Cells(k + 1, 1)).EntireRow.Insert
End If
Next k
End Sub