VBA ClearContents won't clear for hidden rows

[bgrice]

Hi. My code below will not clear the contents in hidden rows. When a filter is applied some rows are hidden and the code only clears the contents for rows that are visible. What am I doing wrong? Surely the solution is not turn the filter off before clearing contents. Thanks.

Sub ClearAll()
Dim Config As Long
Dim Ans As Integer
    If WorksheetFunction.CountA(Range("A3:A10000")) = 0 Then
        MsgBox "Error: No products selected."
    Else
        Config = vbYesNo + vbQuestion + vbDefaultButton2
        Ans = MsgBox("Are you sure you want to clear all?", Config)
        If Ans = vbYes Then
            ActiveSheet.Unprotect Password:="Test"
            Worksheets("Sheet1").Range("A3:A10000").ClearContents
            ActiveSheet.Protect Password:="Test"
        End If
    End If
End Sub

 

[Siddharth Rout]

Pardon me if I have misunderstood you but If you really want to clear everything then what is the point of that filter.

If you want to keep the filter and still clear all then you do not need to turn the filter off. You can keep the filter and show all data or clear the filter. For example

Worksheets("Sheet1").ShowAllData

or even

With Worksheets("Sheet1").Range("$A$1:$A$16")
    .AutoFilter Field:=1
    .ClearContents
End With

 

[bgrice]

Thanks for your reply. The filter is needed as it is hiding data in other columns.

I have just done a test though and it appears if the row is hidden manually then clearcontents will clear the data in any hidden row, but if the row is hidden because of a filter then clearcontents will not clear the data. This is odd in my opinion but that's what the test suggests.

So the answer then is, as you say, to show all data or clear the filter before clearing contents.

 

[Siddharth Rout]

Thanks for your reply. The filter is needed as it is hiding data in other columns.

I have just done a test though and it appears if the row is hidden manually then clearcontents will clear the data in any hidden row, but if the row is hidden because of a filter then clearcontents will not clear the data. This is odd in my opinion but that's what the test suggests.

So the answer then is, as you say, to show all data or clear the filter before clearing contents.

In such a case try this (UNTESTED)

Dim rng As Range
Dim aCell As Range
Dim rngHidden As Range

'
'~~> Rest of your code
'

If Ans = vbYes Then
    ActiveSheet.Unprotect Password:="Test"
    With Worksheets("Sheet1")
        '~~> This is your range
        Set rng = .Range("A3:A10000")

        '~~> Clear the range
        rng.ClearContents

        '~~> Loop through the range and identify the hidden rows
        For Each aCell In rng
            If aCell.EntireRow.Hidden Then
                If rngHidden Is Nothing Then
                    Set rngHidden = aCell
                Else
                    Set rngHidden = Union(rngHidden, aCell)
                End If
            End If
        Next aCell

        '~~> Clear the hidden cells if found
        If Not rngHidden Is Nothing Then rngHidden.ClearContents
       
    End With
    ActiveSheet.Protect Password:="Test"
End If