Home » Forums » Open Source Learning Center » Question and Answer

select the first cell containing data after using autofilter

Submitted by Sandra on 3 November, 2012 - 18:17

Hi Experts !
How do I select the first cell containing data (if any) after using autofilter ?
thanks

‹ sheet name verification in formula Match list of data of header name and High light in colour unmatched data (vb or macro ) ›
»

try this

Submitted by Ashish Koul on 5 November, 2012 - 16:09.

Sheets(1).Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas(2).Columns(1).Cells(1, 1).Select

»
Nick's picture

nice 1 Ashish... I have a

Submitted by Nick on 5 November, 2012 - 17:42.
nice 1 Ashish...

I have a suggested improvement: 

Sub SelectFirstPopulatedAutofilteredCell()
If Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
 
 Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Columns(1).Cells(1, 1).Select
Else
 MsgBox "No populated cells"
End If
 
End Sub

- to account for data with blank rows, and when the autofilter does not return anything

... however, a bug remains in that if blank cells are included in the autofilter, they're not selected

»