Excel VBA deleting hidden table rows

Question

I need help with a VBA macro please. I am trying to delete hidden table rows, as well as table headers to merge ranges of multiple tables. They work as separate macros, but when I put the macros together I get the run-time error '1004': Delete method of Range class failed:

Sub delHR()

    ' Hide table header rows
    Rows("14:15").EntireRow.Hidden = True
    Rows("26:27").EntireRow.Hidden = True

    ' Delete unselected, i.e. hidden table rows
    Dim hR As Long
    Dim lastRow

    lastRow = 50
    For hR = lastRow To 1 Step -1
    If Rows(hR).Hidden = True Then Rows(hR).EntireRow.Delete
    Next

End Sub

I have tried converting the tables to ranges (which I only need to select rows to be used from the tables) but that unhides unselected rows, which I need to remain hidden so they can be deleted. Thanks.

Bernd Stoeckel Bernd Stoeckel · Accepted Answer · 2016-12-06T14:11:13

Specify the addressed Rows, so that vba knows:

(Alter 'ActiveWorkbook' and the worksheet to your need)

Sub delHR()
With ActiveWorkbook.Worksheets("Temp")
    ' Hide table header rows
    .Rows("14:15").EntireRow.Hidden = True
    .Rows("26:27").EntireRow.Hidden = True

    ' Delete unselected, i.e. hidden table rows
    Dim hR As Long
    Dim lastRow

    lastRow = 50
    For hR = lastRow To 1 Step -1
    If .Rows(hR).Hidden = True Then .Rows(hR).EntireRow.Delete
    Next
End With
End Sub

Excel VBA deleting hidden table rows

1 Answers