Selecting the next cell in a column using filters in a Macro

[ireland13752]

Hello,

I need to select the next cell from A1 using a Macro that will use filters for data and then input with drop down to last row.
The issue I am running into is that the data will be in different rows everytime. The filter will remain the same and in the same column but it may be in row 9 today and row 2 next week.

See below for current code.

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AS$68").AutoFilter Field:=31, Criteria1:= _
"Liver County Midevil Center"
Range("A9").Select
ActiveCell.FormulaR1C1 = "LCMC"
Range("A2").Select

lastrow = Range("C35000").End(xlUp).Row
Selection.AutoFill Destination:=Range("A2:A" & lastrow)


I need the --Range("A9").Select-- to not reference the cell specifically but instead go to the next nonfiltered cell after A1. I tried R2C1 but am not sure how to apply it.

Seems like an easy one, maybe I am just having a brain fade moment... Thanks for your help

 

[crouchsw]

ActiveSheet.Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Select

That will find the next visible cell below A1

 

[SirJB7]

@crouchsw
Hi!
2 points, just for the Range method part only:
a) From 2007 version row ranges up to 1048576.
b) Being purist, last row border conditions should be trapped like this:

    With ActiveSheet
        .Cells(.Rows.Count, 1).Select
        If .Cells(.Rows.Count, 1).Value = "" Then Selection.End(xlUp).Select
    End With

Regards!

 

[ireland13752]

@ SirJB7 Using this, changing a couple things and adding couple more steps, it worked! Thank you very much! I am truly grateful!!!

 

[SirJB7]

Hi, ireland13752!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!