Check For Visible Cells After AutoFilter

  • I'm working on a VB code where I use an autofilter. I want to clear a selection in a couple of columns, where the value in the autofilter = 1.


    This is the code:


    Sometimes there is no value = 1, which results in clearing row no.1 because this row was still selected for the autofilter.
    I'd like to be able to check if there are any visible cells, before I proceed with the clearing of the selection.


    I tried this:

    Code
    If Selection.RowHeight <> 0 Then Range("J2:J" & lRow1).SpecialCells(xlCellTypeVisible).Select
        Selection.ClearContents


    But this doesnt work, because row no.1 is still selected.


    Can I use an If then statement to check If there are any visible cells, then clear these, if not, resume next.


    Thanks in advance.

  • Re: Select Visible Cells; Nothing To Select


    Hi


    Code
    Dim rng As Range
    On Error Resume Next
    Set rng = Range("F2:H" & lRow1).SpecialCells(xlCellTypeVisible)
    On Error Goto 0
    If Not rng Is Nothing Then
      'proceed with original clear contents operations
    End If


    Richard

  • Re: Check For Visible Cells After AutoFilter


    As soon as you use Select ALL cells are selected.


    Code
    On Error resume next
    Sheet1.UsedRange.Columns(1).SpecialCells(xlCellTypeVisible).Offset(0,[B][U]Columns[/U][/B]).Clear
    On error goto 0

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!