Excel VBA to delete rows with a blank value in first column?

[AppStaff]

Dim RowStart As Range

Dim RowFinish As Range

Worksheets("sheet1").Activate

Set RowStart = Cells("A11")
Set RowFinish = Cells("A500")
RowStart.Select
    Do While ActiveCell < Cells(RowFinish)
        If ActiveCell = "" Then
        Rows(ActiveCell).Select
        Selection.Delete Shift:=xlUp
        ActiveCell.Move
        End If
    Loop

I'm trying to write a macro that will check a column for null values and then delete all rows that qualify. This is as far as i've gotten but I'm pretty new to excel code and I think I'm missing a bunch of pieces. Can anyone help me out?

 

[xlbo]

1: When deleting in a loop, work from the BOTTOM UP - for that, you will need a loop structure like:

for i = 500 to 11 step -1
'process here
next i

2: looping, in this instance is actually quite in-efficient - best bet is to AUTOFILTER for BLANKS - delete all rows except header then turn autofilter off. Easiest way to get the code for that is to record yourself doing it. If you then want any pointers or examples of how to shorten the code etc, please feel free to post it with your specific circumstances

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AppStaff]

Thanks, I'll look into the autofilter option and see if I can make that work.

 

[ManxDom]

To shorten the time it takes for the deleting code to run, try turning off screen updating

this can easily be done with
Application.ScreenUpdating = False

dont forget to turn it back on again though before the code ends with
Application.ScreenUpdating = True

Dom