Deleting Blank Rows (VBA)

khawarameer

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

Some videos you may like

Excel Facts

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

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
thats what I like about this board

you always find better ways of doing stuff...
 

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
151

ADVERTISEMENT

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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It only deletes the rows that have blanks in column A.
 

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
151

ADVERTISEMENT

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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
No need for VBA here. Just filter column A for blanks and delete the visible rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

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
Top