select last visible cell in autofilter

[keithkemble]

HI Folks,

I have having difficulty in determining how to find the last visible cell in a filtered selection and would appreciate any pointers

(Objective - select visible range - to select first visible cell column A to last visible cell column k then clear contents) having already filtered for 3 criteria (first data row 13)

The selection must only be visible cells. and would use the the clearcontents command for the range as there is data to the right of column K.

I currently have a macro that asks for 3 lots of criteria for the autofilter
Then filters leaving the visible cells

Then finds the first visible cell - which is stored to a variable

Unable then to determine / find / locate (via VBA) the last visible cell/ row (endup etc does not find the visible only the last physical cell/row)

thanks

 

[keithkemble]

cheers Rick,

I will experiment with the functions later once I have finished embellshing my current routine
Thanks

 

[p45cal]

Does this do it?:

Set zzz = ActiveSheet.AutoFilter.Range
zzz.Offset(1).Resize(zzz.Rows.Count - 1, 11).SpecialCells(xlCellTypeVisible).Select 'or .clearcontents or .clear

and to cater for no filtered cells remaining:

Dim yyy As Range
Set zzz = ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yyy = zzz.Offset(1).Resize(zzz.Rows.Count - 1, 11).SpecialCells(xlCellTypeVisible)    '.Select 'or .clearcontents or .clear
On Error GoTo 0
If Not yyy Is Nothing Then yyy.Select    'or .clearcontents or .clear