Issue

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.

Solution

Try this macro

when you invoke the 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

Note

Thanks to venkat1926 for this tip on the forum.