# 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


----------



## Sektor

Assuming that filter is in the first row.


		Code:
__


[COLOR="Blue"]Function[/COLOR] AllVisibleCells() [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Set[/COLOR] AllVisibleCells = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]


----------



## Domenic

To select the first visible cell in Column Q, maybe...



		Code:
__


[font=Verdana][color=darkblue]Sub[/color] SelectFirstVisibleCell()

    Range("Q2", Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]


----------



## awagdarikar

Hi Domenic,


Sub SelectFirstVisibleCell()    Range("Q2", Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select    End Sub</PRE> 
doesn't work. It selects the Q1 cell. For your reference my data is as below. i apply an autofilter in cell Q1 (Parent Name) to select blank cells.

<TABLE style="WIDTH: 372pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=495 border=0><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 51pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" width=68 height=21>*to Cavity*</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=74>*to plating*</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 158pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=211>*Parent Name*</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 107pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=142>*Ckt Name wo Suffix*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SH2011_2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1546</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SH2011_2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1947</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>14</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">115</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>14</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">115</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>14</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">116</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>14</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">116</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>40</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">117</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>94</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SN</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">117</TD></TR></TBODY></TABLE>

My code for fliter is as follows 

Sheets("Input for Pivot").Select
'removes AutoFilter if one exists
    Worksheets("Input for Pivot").AutoFilterMode = False


    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Worksheets("Input for Pivot").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Input for Pivot").Sort.SortFields.Add Key:=Range( _
        "Q2:Q" & Range("A1048576").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Input for Pivot").Sort
        .SetRange Range("A1:R" & Range("A1048576").End(xlUp).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Application.Goto Reference:="R1C17"
    ActiveSheet.Range("$A$1:$R$" & Range("A1048576").End(xlUp).Row).AutoFilter Field:=17, Criteria1:="="
End Sub

Thanks,
awagdarikar


----------



## Peter_SSs

awagdarikar 

I'm wondering what you are going to do after selecting that cell, given that you rarely need to select a cell/range to work with it in vba and selecting slows your code.


----------



## awagdarikar

Thanks for your reply Peter,

As this column Parent Name is used as first column in the pivot in next process blank cells in it cause a huge problem. So I first apply filter to this column to make all blank cells visible one below the other. This is because the blank cells are scattered across this column. In the next step i put a unique reference text through formula in all selected blank cells which is based on other column in the same row.
My method is to put formula in the first visible cell and then copy it to rest of the cells. I use code to pu formula as follows



		HTML:
__


ActiveCell.FormulaR1C1 = _
        "=IF(CODE(LEFT(R[-14]C[-9],1))=83,""(""&R[-14]C[-9]&"")"",R[-14]C[-9]&"" (Not_Shielded)"")"
    Selection.FillDown

 
Please suggest,
awagdarikar


----------



## Rick Rothstein

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).


		Code:
__


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


----------



## Peter_SSs

awagdarikar said:


> Thanks for your reply Peter,
> In the next step i put a unique reference text through formula in all selected blank cells which is *based on other column in the same row*.


I'm not sure how. The R1C1 formula you provided refers to the row 14 rows above the row the formula occupies.

What would the actual formula in the sheet be in Q40 if that, say, was the first blank cell in column Q?


----------



## awagdarikar

The formula code is generated for row 2. 

The reason is in abcense of the code to select first visible cell under filter  I have created a workaround wherein i add a blank row in row 2 and paste this formula in Q2. In next step i delete this row after copying it to all blank cells below it in the range. So i will not get any errors .

If used for the active cell it should point to the same row.


----------



## awagdarikar

Thanks for suggestion Rick,

However this code does not move cursor


----------



## 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


----------



## Peter_SSs

awagdarikar said:


> The formula code is generated for row 2.
> 
> If used for the active cell it should point to the same row.


Again I don't see how. If it was pointing to the same row then it would use RC[-9] not R*[-14]*C[-9]

Take a blank worksheet and run this code
	
	
	
	
	
	




		Code:
__


Sub TestFormula()
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(CODE(LEFT(R[-14]C[-9],1))=83,""(""&R[-14]C[-9]&"")"",R[-14]C[-9]&"" (Not_Shielded)"")"
End Sub

For me it produces this formula in Q2
=IF(CODE(LEFT(H1048564,1))=83,"("&H1048564&")",H1048564&" (Not_Shielded)")

Surely that is not what you meant? 
That is why I asked for the *actual worksheet formula *for a particular cell.


----------



## awagdarikar

The actual worksheet formula in the cell Q2 is
=IF(CODE(LEFT(H2,1))=83,"("&H2&")",H2&" (Not_Shielded)")

When it is recorded in correct cell it will be as below


		HTML:
__


ActiveCell.FormulaR1C1 = _
        "=IF(CODE(LEFT(RC[-9],1))=83,""(""&RC[-9]&"")"",RC[-9]&"" (Not_Shielded)"")"

 
By mistake I had entered the formula at wrong cell to generate code. that is why it pointed some other row. Sorry for confusion,


----------



## Rick Rothstein

awagdarikar said:


> Thanks for suggestion Rick,
> 
> However this code does not move cursor


I know... I said it gives you the row number of the first visible row in the autofiltered range... you can use that to form your cell reference. I was (and still am not) clear as to what "first visible cell" means. If you know the column, let's say it is Column Q, then the next you would want this statement in your VB code...



		Code:
__


Cells(GetFilteredRangeTopRow, "Q").Select

The part I wasn't clear on is whether the column is always Q or if there is some other condition dictating the column letter.


----------



## Peter_SSs

awagdarikar said:


> The actual worksheet formula in the cell Q2 is
> =IF(CODE(LEFT(H2,1))=83,"("&H2&")",H2&" (Not_Shielded)")
> 
> When it is recorded in correct cell it will be as below
> 
> 
> HTML:
> __
> 
> 
> ActiveCell.FormulaR1C1 = _
> "=IF(CODE(LEFT(RC[-9],1))=83,""(""&RC[-9]&"")"",RC[-9]&"" (Not_Shielded)"")"
> 
> 
> By mistake I had entered the formula at wrong cell to generate code. that is why it pointed some other row. Sorry for confusion,


Thanks, that's what I was after.

I have two ideas which I suggest you try in a copy of your workbook.

First, at the end of your previous code add this

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> ActiveSheet.AutoFilter.Range<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    Intersect(.Offset(1).Resize(.Rows.Count - 1), Columns("Q")) _<br>        .SpecialCells(xlCellTypeVisible).FormulaR1C1 = _<br>        "=IF(CODE(LEFT(RC[-9],1))=83,""(""&RC[-9]&"")"",RC[-9]&"" (Not_Shielded)"")"<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>



My second suggestion is wondering whether you need to do all the sorting and filtering at all. Provided you don't have a large data set with too many separate areas of blank cells, you could try this as stand-alone code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Alternative()<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    Range("Q1:Q" & Range("A" & Rows.Count).End(xlUp).Row) _<br>        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _<br>        "=IF(CODE(LEFT(RC[-9],1))=83,""(""&RC[-9]&"")"",RC[-9]&"" (Not_Shielded)"")"<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


----------



## awagdarikar

You are amazing!!!!!!
Thanks a million,

My entire code of 83 lines was reduced to 3 lines.
However I wonder if i could add following to my knowledge,

1. How do i add shading in background for the cells containing formula?
2. How do i apply filter to cells containing formulas? I have purposely kept characters () in the formula so that i could manually apply filter if required for debugging. However i discovered text filter does not work for character "(" or ")"

any further help will be greatly appreciated,

Cheers


----------



## Peter_SSs

awagdarikar said:


> You are amazing!!!!!!
> Thanks a million,


Glad to help. Thanks for the enthiusiastic endorsement. 




awagdarikar said:


> 1. How do i add shading in background for the cells containing formula?


Add the blue line of code
	
	
	
	
	
	




		Rich (BB code):
__


Sub Alternative()
    On Error Resume Next
    Range("Q1:Q" & Range("A" & Rows.Count).End(xlUp).Row) _
        .SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 35
    Range("Q1:Q" & Range("A" & Rows.Count).End(xlUp).Row) _
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IF(CODE(LEFT(RC[-9],1))=83,""(""&RC[-9]&"")"",RC[-9]&"" (Not_Shielded)"")"
    On Error GoTo 0
End Sub






awagdarikar said:


> However i discovered text filter does not work for character "(" or ")"


It does for me. I tried 'Contains' ( and I also tried 'Begins with' ( and they both worked (with different number of rows showing)


----------



## awagdarikar

Thanks Peter,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
All your solutions worked. You guys are amazing ambassador's of excel,<o></o>
This has further enhanced my trust on MrExcel as resource to depend on!!!<o></o>
 <o></o>
awagdarikar<o></o>


----------



## finaljustice

Isn't there a easier way to select the first visible cell after the filter is applied?? Something link ".offset(1,0)"?

I just want to know how to tell VBA that the to move one cell down after applying filter so I can continue my coding.

What I've seen here is something as selecting a visible range after applying the filter, is that the only / best way to deal with that?

Thank you for your attention.
Cheers,
Final


----------



## pgarakani

Hello,

I have a similar issue I need help with.  I have a file that is recieved as a TXT file and is already given a name.  This name will change every week but will have same format..."Inserts SD WK XX 2012".  I need a macro that will run on this 1 Sheet file every week.  The number of rows will vary from week to week so a good range needs to be set.  The first step is to filter by SUB_DISTRICT (Column D) and select only the four (4077, 4751, 4775, and 5771) I need.  Then I need to go to the first cell below the header row in a column K and enter a formula that multiplies the first cell below header row in Column H by first cell below header row in Column J.  Then copy this formula all the way down Column K til the data ends.  This is all I need.  This is as far as I got...



		Code:
__


Sub SanDiegoInsertsProcess()
'
' SanDiegoInsertsProcess Macro
' Runs Complete San Diego Inserts Process
'
'
    Columns("D:D").EntireColumn.AutoFit
    Selection.AutoFilter
    ActiveSheet.Range("$D$1:$D$17853").AutoFilter Field:=1, Criteria1:=Array( _
        "4077", "4751", "4775", "5771"), Operator:=xlFilterValues
End Sub


----------



## IvanDIP

Maybe I'm saying something foolish
but me too I had the same problem, and being a beginner I had solved it in that way.
Is it so wrong?

I select a cell on the header and than scroll down a lot... 

    Range("*Table_Name*[[#Headers],[*Name_of_a_cell_of_the_headers*]]").Select
    ActiveWindow.SmallScroll Down:=-1000000


----------



## 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

Rick Rothstein said:


> 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).
> 
> 
> Code:
> __
> 
> 
> 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

Rick Rothstein said:


> 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).
> 
> 
> Code:
> __
> 
> 
> 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

Rick Rothstein said:


> 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).
> 
> 
> Code:
> __
> 
> 
> 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. !!!


----------

