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.

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

[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

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

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]

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