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

- - Latest reply: venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Nov 27, 2009 at 07:26 PM
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
See more 

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
784
0
Thank you
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
784
0
Thank you
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