VBA IF function: when two cells are different Closed

Question

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

venkat1926 · Answer

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

venkat1926 · Answer

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

VBA IF function: when two cells are different

2 responses

Similar discussions

Subscribe To Our Newsletter!