Select first visible cell under the header row after applying autofilter

[awagdarikar]

Hello,​

I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?​

I tried​

http://www.mrexcel.com/forum/showthread.php?t=403989

but it does not work. Probably because column Q is filtered to show only blank cells,

Can somebody help me on this?

Thanks,
awagdarikar

 

[Scoox]

Does this function help you any? It returns the row number of the first visible row in an autofiltered range (it returns 0 if all the cells in the autofiltered range are hidden).

Function GetFilteredRangeTopRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
  End With
NoFilterOnSheet:
End Function

Neat! Just what I was looking for.

 

[vinmam123VBA]

Does this function help you any? It returns the row number of the first visible row in an autofiltered range (it returns 0 if all the cells in the autofiltered range are hidden).

Function GetFilteredRangeTopRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
  End With
NoFilterOnSheet:
End Function

This is really good one! It helped me a lot!!

Thank you for posting this

Regards,
Vinmam

 

[Spyros13]

Does this function help you any? It returns the row number of the first visible row in an autofiltered range (it returns 0 if all the cells in the autofiltered range are hidden).

Function GetFilteredRangeTopRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
  End With
NoFilterOnSheet:
End Function

Genius !! Thanks !!

I know its old , but thank you very much.

Dear op or anyone reading this after so many years,
just reference the function at the end after processing your macro all as usual , so you process that 1st visable row that was aluding you .

Like this, after your main sub or loops: ... Range("A" & GetFilteredRangeTopRow).Select ... etc.

There are always edge cases, and this line of thinking to handle was genius. Youve helped Rick Rothstein & you always help, no end. !!!