Deleting Blank Rows (VBA)

[khawarameer]

Dear All,

I have a large worksheet with approx 55600 rows. However many rows in the sheet are empty. I used a macro to delete empty rows but it is giving me "Run time error '6' ". Here is the code i am using

Sub Deleterow()

Dim xlastrow As Integer
Dim xrow As Integer
xrow = 1

Range("a65000").End(xlUp).Select

xlastrow = ActiveCell.Row

Do Until xrow = xlastrow

If Cells(xrow, 1).Value = "" Then
Cells(xrow, 1).Select
Selection.EntireRow.Delete

xrow = xrow - 1
xlastrow = xlastrow - 1

End If

xrow = xrow + 1

Loop
End Sub

Can any one help me with this.

Regards

Khawar Ameer Malik

 

[baitmaster]

try this

Sub Deleterow()

Dim xlastrow As long ' integers only go to 32000 in XL

xlastrow = Range("a65000").End(xlUp).row

for i = xlastrow to 1 step -1
    If Cells(xlastrow, 1).Value = "" Then rows(xlastrow).delete shift:=xlUp
next i
End Sub

 

[VoG]

This should be faster

Sub DeleteRow()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[baitmaster]

thats what I like about this board

you always find better ways of doing stuff...

 

[khawarameer]

This should be faster

Sub DeleteRow()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Well this code deleted all my rows. Luckily i have a copy of my sheet. My there is some thing missing in this code.

 

[VoG]

It only deletes the rows that have blanks in column A.

 

[khawarameer]

It only deletes the rows that have blanks in column A.

I have numeric data in some rows of Colum A and some row are blank but it has deleted all.

 

[VoG]

I have tested it. It only deletes blanks.

 

[Tom Urtis]

Do this...

Take a close look at what the last data-containing cell is in column A. I know you said approximately 55600, but take a moment to make note of the actual row.

Let's say the actual cell with data is A55642.

In some empty cell outside of column A, enter the formula
=COUNTBLANK(A1:A55642)
remember to insert the proper cell address in the formula for whatever the last cell is.

My guess is, the number returned by that formula wil be higher than 8192 which is the specialcells limit, because of the 55600+ possibles.

SpecialCells will still work but it needs to be chopped up into a loop iteration for sets of ranges within the realm of 8192 possibles.

Shouldn't be a problem but look to make sure it really is column A you are talking about (sounds silly but make sure) and see how many blank cells there are.

 

[Colin Legg]

No need for VBA here. Just filter column A for blanks and delete the visible rows.