Remove rows with empty cells in column 1

[d_rose]

Hi All, I'm new to VBA and trying to write a macro that will delete all the rows that contain a blank cell in column 1 (A), I'm attaching an example of my spreadsheet and the macro I've come up with so far. The problem is that the macro loops continuously and the only way I can stop it is to end the application which obviously loses the data.

Any help is greatly appreciated


Thanks, dave

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[AB33]

Because this line is not going be true

Please Login or Register  to view this content.

May be

Please Login or Register  to view this content.

Assuming you are dealing (selecting) with column "A".

[d_rose]

You my friend are The Man
I just tried it and it works PERFECTLY

Many Thanks,
dave

[d_rose]

Hi AB33, I tried pasting your code into my original macro (deleting my "Do Until" statement) but received a Object doesn't support this property or method error as soon as it hit the new line. But I do appreciate your trying.

Thanks,
dave

[AB33]

No, your code will not work. If you just want to delete empty columns in A, you can not say until that column is blank.
Instead, you can use either of these codes. The first code just simple deletes the row if it finds empty cell in column A, while the second one is the same but it stops when column A finds Ref. No-That is where the codes stops and would not look for empty column A

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[d_rose]

Hi AB33, I tried both your codes, the first worked fine but the second went into an endless loop.

Still, thanks for your efforts.

dave

[AB33]

Because of this line

Do Until Cells(i, 1).Value = "Ref. No."

to test it put "Ref. No." in any column "A".
If you do not have a cell with "Ref. No." in column A, it is going to loop indefinitely. My assumption of what were trying to do was to loop throught column A until it find "Ref. No.". Until this point, the code will look for a blank in column A, if it finds it, it delete it.
However, loop until column A is blank while trying to find a blank cell in column A will not work and would not make any sense either.
I hope this make sense