Check For Visible Cells After AutoFilter

dsil · Jul 30th 2008

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:

Code

Dim lRow1 As Long
    lRow1 = WorksheetFunction.Max(Range("A65536").End(xlUp).Row)


Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=9, Criteria1:="1"


Range("F2:H" & lRow1,"J2:J" & lRow1).SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents

Display More

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.

RichardSchollar · Jul 30th 2008

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

Dave Hawley · Jul 31st 2008

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

dsil · Jul 31st 2008

Re: Check For Visible Cells After AutoFilter

Thanks guys, I got it working with richards tip!

Check For Visible Cells After AutoFilter

Participate now!

Share