VBA - Using the IF function for 2 cells


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.


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.


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


Thanks to venkat1926 for this tip on the forum.

Spread the love

Leave a Comment