How to delete blank row by VBA ?

sthanawa

Board Regular
Joined
Jul 5, 2006
Messages
80
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
L

Legacy 68403

Guest
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
Along the lines of what Jindon said, try this:
Code:
Sub MyDeleteRows()
    
    Range("A1:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,950
Messages
5,545,139
Members
410,665
Latest member
origamistudios
Top