Macro Help - Clearing Cell Contents

Posted by Jammer on September 20, 2001 5:01 AM

I have an interesting question. I have a spread sheet of variable size. I want to make a macro that will clear the contents of two cells drop down two cells and clear two....and so on. Since this size of the worksheet is always changing, I can't just create one macro to do it all at once. I there a way to do this?

(i.e. column A has all "1's" say for 10 rows. I want to clear rows 2 and 3, keep 4, clear 5 and 6, keep 7, clear 8 and 9, clear 10, etc....)

Thanks for the help.

Posted by Rob Jackson on September 20, 2001 5:27 AM

This code will do the job you may need to tweak it for your use but all you do is select a cell in the first row you want blanked then run the code.

Sub ClearUp()
Dim Startpoint As Long
' Finds starting row
Startpoint = ActiveCell.Row

' Loops down rows by 2
For Clearloop = Startpoint To 65536 Step 2
' Checks if cell is blank looking for end of sheet then stops loop
If Range("A" & Clearloop).Value = "" Then
Exit For
End If
' Clears contents of Rang A to B on that line.
Range("A" & Clearloop & ":B" & Clearloop).ClearContents
Next Clearloop
End Sub

Hope it helps.