Help with reparing code for removing blank rows in a range

[bootj1234]

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

 

[Fluff]

Is the sheet protected?
Do you have any merged cells?

 

[bootj1234]

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

 

[smozgur]

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

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

 

[Fluff]

You're welcome & thanks for the feedback

 

[bootj1234]

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