How To Select First Visible Cell After Applying a Filter

Krucifire · Jul 21, 2009

Hi Guys,

Running the Following Code which works a Treat But I can Figure out How after I get the Filters to apply how i Select the First Visible Cell

Code:

Worksheets("Trade_List").Select
With Worksheets("Trade_List")
    .AutoFilterMode = False
    Range("C3").Select
    Selection.AutoFilter field:=3, Criteria1:=ComboBox1.Value
    Range("B3").Select
    Selection.AutoFilter field:=2, Criteria1:=ComboBox2.Value
    Range("AL2").Offset(1, 0).Select
    
   
End With

The Table has Two Rows as a Header to the Data so as you can see filters apply. After the Filters apply there will only be 1 row of Data Visible i Want to take the data from within Column "AL" Row "X", Where "X" is the Visible Row and Display it in UserForm1.Textbox1.Text

But i cant remember how to select the Visible row/Cell

any ideas??

Sandeep Warrier · Jul 21, 2009

Hi,

You can try this. It's a very cheeky solution and the VBA masters here would frown at it, but it gets the job done.

After you are done filtering, use the code

Code:

        Range("AL2").Select
        ActiveCell.Offset(1, 0).Select
        If Cells(Columns("AL").Rows.Count, "AL").End(xlUp).Row > 3 Then
            Range(Selection, Cells(Columns("AL").Rows.Count, "AL").End(xlUp)).SpecialCells(xlCellTypeVisible).Select
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Offset(0, -1).Select
        End If

Since you said that you had 2 rows with headers, I'm assuming the Row1 and Row2 are the header rows and the data starts from Row3. If not, you can change the >3 part to the row number from which the actual data begins
<input id="gwProxy" type="hidden"><input *******="jsCall();" id="jsProxy" type="hidden">

Krucifire · Jul 21, 2009

Thanks Mate worked a treat.

Sandeep Warrier · Jul 21, 2009

Krucifire said:
Thanks Mate worked a treat.

You're welcome.

....After the Filters apply there will only be 1 row of Data Visible....

I read this part late. If you have only 1 row of results, then even this will work.

After the filtering code

Code:

Cells(Columns("AL").Rows.Count, "AL").End(xlUp).Select

Boller · Jul 21, 2009

For any number of visible data rows :-

Code:

Range([AL3], Cells(Rows.Count, "AL")).SpecialCells(xlCellTypeVisible)(1).Select

Or the whole code :-

Code:

Worksheets("Trade_List").Select
ActiveSheet.AutoFilterMode = False
[C3].AutoFilter field:=3, Criteria1:=ComboBox1.Value
[B3].AutoFilter field:=2, Criteria1:=ComboBox2.Value
Range([AL3], Cells(Rows.Count, "AL")).SpecialCells(xlCellTypeVisible)(1).Select

How To Select First Visible Cell After Applying a Filter

Krucifire

Board Regular

Excel Facts

Sandeep Warrier

Well-known Member

Krucifire

Board Regular

Sandeep Warrier

Well-known Member

Boller

Banned

Similar threads

Forum statistics

Share this page

How To Select First Visible Cell After Applying a Filter

Krucifire

Board Regular

Excel Facts

Sandeep Warrier

Well-known Member

Krucifire

Board Regular

Sandeep Warrier

Well-known Member

Boller

Banned

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock