Help with reparing code for removing blank rows in a range

bootj1234

Board Regular
Joined
Aug 27, 2012
Messages
85
The following code for deleting blank rows within a range has stopped working for me. I need help with direction for how to make it start working again.

Here is the message dialog box that popped up when I did a "Step In" to see if I could figure out what was wrong with the code:

Run-time error '1004':
Delete method of Range class failed

Thanks

- - - - - - -

Sub DeleteRow()
Dim lLastRow As Long, l As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row

'Start at bottom row and work up finishing at row 2(change as required)
For l = lLastRow To 11 Step -1
If Range("A" & l) = "" Then Rows(l).Delete
Next
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Is the sheet protected?
Do you have any merged cells?
 
Upvote 0
Hi Fluff,

Thanks for the suggestions. I went back and check and there was an indecent in which I had a couple of merged cells. After I un-merged the cells the macro works just like it used to.

I really appreciate your quick response!

Best regards - John
 
Upvote 0
I can see the problem was solved already (thanks to Fluff!), but I always try to keep away from deleting rows one by one, so I prepared the following macro as an alternative way - it will still fail with merged cells anyway :)

Code:
Sub deleteBlankRows()
Dim sht As Worksheet
Dim rng As Range


    'SpecialCells method forces to use Error Handling
    On Error GoTo noBlanksInRange
    
    'Active or another sheet
    Set sht = ActiveSheet
    
    'Define the working range, it starts from A11, until the last used cell in the column
    'Then use SpecialCells method to find the blank cells in the range
    Set rng = sht.Range(sht.Cells(11, 1), sht.Cells(sht.UsedRange.Rows.Count, 1)).SpecialCells(xlCellTypeBlanks)
    
    'Delete the entire rows where blank cells reside in A column
    rng.EntireRow.Delete xlUp
    
noBlanksInRange:
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hi smozgar,

Thanks for your response and code recommendation. I gave it a try and it worked really well.

I am glad you passed this along!

Best regards - John
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

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