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

 

[Peter_SSs]

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

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

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]

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

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]

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

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]

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

Glad to help. Thanks for the enthiusiastic endorsement.

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

Add the blue line of 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

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

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