How to delete blank row by VBA ?

[sthanawa]

Hi All ,

I have one question . in the colume A , row 1 until row 1000 . Some cells contain string value , some cells were absolute blank.

How can I delete the blank cells or delete by row up with VBA ? please suggest.

 

[Legacy 68403]

try the following formula

Range("A1").select
Do While Not IsEmpty(ActiveCell)
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.Delete Shift:=xlup
Loop

 

[jindon]

Hi

Use Autofilter or specialcells method, so that you don't need to loop.

 

[Joe4]

Along the lines of what Jindon said, try this:

Sub MyDeleteRows()
    
    Range("A1:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub