Need Help Creating a Macro that will unfilter the entire sheet, and then create a filter in a specific row (different on each sheet)

[dnorber]

So I work with a lot of spreadsheets where there are filters, however, the filter is on a different row in each spreadsheet. My feeling is that there is a macro I can create that will unfilter a sheet (easy enough right, select the entire sheet and then do data: filter: unclick autofilter). The trick is determining which row to create the new filter.

It seems to me, the best way to do this is to ensure that Column A is clear until the row I want to filter (that is, the first row Column A that has information entered in it is the one that I want to filter).

I just don't know the VBA command to look for the first cell that has an entry.

Can anyone help, or know how to create that Macro?

Thank you!

 

[texasalynn]

welcome to MrExcel Board....

try this to find the first cell with data in column

Sub firstrow()
x = Range("A1").End(xlDown).Address(False, False)
MsgBox "First row with data " & x
End Sub

 

[dnorber]

Thanks for the welcome. I grabbed an answer off the board last week, and it was perfect, so I am hopeful that the experts here can help with my infrequent questions as well.

the macro you have suggested works perfectly to identify the 1st cell in Column A that has data. How can I convert the second part of this macro to, instead of returning a message box that tells me where the first set of data is, to highlighting the entire row, and then doing an auto-filter on that row?

Thanks!

 

[texasalynn]

try this

Sub firstrow()
x = Range("A1").End(xlDown).Address(False, False)
Range(x).AutoFilter
End Sub

 

[dnorber]

YES!

Thank you so much!

Edit: ok, so that works, but I've messed up the the first part of the macro.
What I want to do is select all cells, undo the autofilter, and then create this new autofilter...here is what I am using

Sub firstrow()
Cells.Select
Selection.AutoFilter
x = Range("A1").End(xlDown).Address(False, False)
Range(x).AutoFilter
End Sub

but it doesn't work. It just ends up highlighting my worksheet

 

[dnorber]

FIXED IT.

Sweet.