Inserting a blank row with a macro?

  • Thread starter Thread starter Aaron Russell
  • Start date Start date
A

Aaron Russell

Hello everyone. Is there any way I can create a macro that will compare two
cells such as B1 and B2 and if they dont' match to insert a blank row after
B1? Thanks in advance.

Aaron
 
Sub CompareEm()
If [B1].Value <> [B2].Value Then
[B2].EntireRow.Insert
End If
End Sub

You can usually figure out things like this by recording a macro while
you do it manually, then modify the code.

Jerry
 
Aaron

To compare cells in column B and insert a row at each change in data.

Sub InsertRow_At_Change()
Dim I As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For I = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(I - 1, 2) <> Cells(I, 2) Then _
Cells(I, 2).Resize(1, 1).EntireRow.Insert
Next I
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben Excel MVP
 
Thanks for the feedback Aaron.

Always nice to know what does or doesn't do the job.

Gord
 
I wonder if you could help me with one more thing Gord. I would like to go
down the A column and any field populated with the word "yes" i would like
to clear the contents of that cell. Do you have a macro for this? Thanks.

Aaron
 
Aaron, here is one way,

Sub Delete_Yes()
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet.Range("A:A")
Do
Set c = .Find("Yes", LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.ClearContents
Loop
End With
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **
 
Back
Top