Autofiltering then adding a value to filtered rows

[bbfan23]

Hello,

I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.

For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.

How can I get it to stop at the last row?


Thanks!

Cells.AutoFilter Field:=21, Criteria1:="<>"
Cells.AutoFilter Field:=55, Criteria1:="="

Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[bbfan23]

Sorry but I am completely new to this. How do I integrate it with what I have? It's not placing any x's in the cells.

Here is my macro with yours appended and hide columns added in.

Sub Template_NEW()

Cells.AutoFilter Field:=21, Criteria1:="<>"

Cells.AutoFilter Field:=55, Criteria1:="="

Columns("B:D").EntireColumn.Hidden = True

Columns("G:T").EntireColumn.Hidden = True

Columns("W:W").EntireColumn.Hidden = True

Columns("Y:Y").EntireColumn.Hidden = True

Columns("AA:AZ").EntireColumn.Hidden = True

Dim y As Long
y = Range("BC" & Rows.Count).End(3).Row
Range(Cells(2, "BC"), Cells(y, "BC")).SpecialCells(xlCellTypeVisible).Value = "X"

End Sub


I really appreciate any help you can offer. I've been searching online for days and can't find anything that works (with my limited knowledge).

[JOHN H. DAVIS]

Please use code tags when posting. Refer to Forum Rule No. 3.

Maybe:

Please Login or Register  to view this content.

[bbfan23]

Unfortunately that isn't working right either. The first time it put an x in the first two out of an expected 4 cells. I reset the template and try again and get a "Run-time error '1004': AutoFilter method of Range class failed".

Is there a way to use the code that I supplied but limit it to the first 500 or so rows?

Please Login or Register  to view this content.

My macro seems to work as I need it to except for putting x's in all the rows below 3000.

Thanks again.

[bbfan23]

Just figured it out.

Please Login or Register  to view this content.