Deleting Blank Rows (VBA)

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
152
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
try this

Rich (BB code):
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
 
Upvote 0
This should be faster

Code:
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
 
Upvote 0
This should be faster

Code:
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.
 
Upvote 0
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.
 
Upvote 0
No need for VBA here. Just filter column A for blanks and delete the visible rows.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top