MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP - NEED CELL DATA DELETING MACRO


Posted by EDDIE G on October 12, 2001 7:13 PM

I need a macro that starts with the activecell and then goes down a column every other cell and deletes the data. For example, if I activate cell E7 and run the macro, it should clear the data in E9, E11, E13, and so on for the whole column. Can someone help. I am dumb as a stump with VBA.


Posted by Travis Harr on October 12, 2001 8:44 PM

The following macro will do what you want, but will stop when it sees a cell that you want cleared, if that cell is already blank.

Sub Macro1()
Do While ActiveCell.Offset(2, 0).Range("A1") <> ""
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
Loop
End Sub

Posted by Ramon Wilson on October 12, 2001 8:52 PM

Here's a faster way :-

Posted by Ramon Wilson on October 12, 2001 8:52 PM

Here's a faster way :-

Posted by Travis Harr on October 12, 2001 9:02 PM

Anoter Option

To clear fields through a certain row (regardless if they are blank or not, use this. Change the "1000" to be how many rows you want to go down.

Sub Macro1()
For Counter = 1 To 1000
Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
Counter = Counter + 1
Next Counter
End Sub

Posted by Ramon Wilson on October 12, 2001 10:07 PM

Here's a faster way (no looping and no selecting!) ......

....and doesn't stop at a blank cell if there are more cells with data lower down :-

Sub Delete_Alternate_Cells()
Dim rng As Range
Set rng = Range(ActiveCell, Cells(65536, ActiveCell.Column).End(xlUp))
Application.ScreenUpdating = False
Columns(rng.Column).Insert
With rng.Offset(0, -1)
If ActiveCell.Row Mod 2 = 0 Then
.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,"""",0)"
Else
.FormulaR1C1 = "=IF(MOD(ROW(),2)<>0,"""",0)"
End If
.SpecialCells(xlCellTypeFormulas, 2).Offset(0, 1).ClearContents
.EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub