Rows getting hidden after .RemoveDuplicates from a table despite using EntireRow.Hidden=False

Question

I want to remove duplicates from a table ("Daten") with VBA.

When using the code below, the entire table gets hidden afterwards.

Sub RemoveDuplicates()
Dim rng As Range

    Set rng = ThisWorkbook.Sheets("1. Einfügen aller Touren").ListObjects("Daten").DataBodyRange
    rng.RemoveDuplicates Columns:=6, Header:=xlYes
    
Worksheets("1. Einfügen aller Touren").Rows.EntireRow.Hidden = False

End Sub

Even when using .Rows.EntireRow.Hidden = False its still hidden

Using

Worksheets("1. Einfügen aller Touren").Rows.EntireRow.Hidden = False

in a different module unhides the rows however.

Answer 1

It seems like the issue you're facing is that when you remove duplicates from the "Daten" table using VBA, the entire table becomes hidden, and you want to unhide the rows after removing duplicates. The problem you're encountering might be related to how Excel handles the visibility of rows within a table.

You can try the following VBA code to remove duplicates and unhide the rows within the table:

    Sub RemoveDuplicatesAndUnhide()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    ' Set the worksheet and table references
    Set ws = ThisWorkbook.Sheets("1. Einfügen aller Touren")
    Set tbl = ws.ListObjects("Daten")

    ' Set the range within the table's data body
    Set rng = tbl.DataBodyRange

    ' Remove duplicates
    rng.RemoveDuplicates Columns:=6, Header:=xlYes

    ' Unhide all rows within the table
    tbl.DataBodyRange.Rows.Hidden = False
End Sub