I've been looking unsuccesfully everywhere for an answer to how to create an IF loop for the following situation:
Cell A1: Anne
- 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.
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.
Try this macro
when you invoke the macro...
An input box will come up. fill in the initial cell address for e.g A10.
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
Thanks to venkat1926
for this tip on the forum.
Published by aakai1056
Latest update on March 21, 2012 at 01:08 PM by aakai1056.