VBA - Using the IF function for 2 cells

January 2017


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.


Published by aakai1056. Latest update on March 21, 2012 at 01:08 PM by aakai1056.
This document, titled "VBA - Using the IF function for 2 cells," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).