I'm trying to figure out the issue with my code. I have a spreadsheet where I'd like to run a macro. The number of rows changes constantly as every workbook I'll be working with has a different number of rows.
I'm basically trying to get my VBA code to read the last row of a column that has been filtered out. When the filter is applied it will fill column B with a value of N. My current code works, however, at the end of the table, I'm getting 4 additional "N" values outside the table range or rows. I debug each step and I believe the code
ActiveSheet.Range("$A$1:$R$" & lr).AutoFilter
is causing the issue.
I currently have this code:
I was able to get it to read the number of rows existing in the workbook:
lr = Cells(Rows.Count, 18).End(xlUp).Row
ActiveSheet.Range("$A$1:$R$" & lr).AutoFilter Field:=13, Criteria1:=Array( _
"Attachment does not exist in document place holder", _
"Document place holder does not exist", _
"More than one current version files exist in the document"), Operator:= _
xlFilterValues
Range("B2:B" & ActiveSheet.UsedRange.Rows.Count + 1).SpecialCells(xlCellTypeVisible).Value = "N"
However, at the end of the table, there are additional N values outside the table itself. I did a debug and I believe its the part highlighted in red that's giving me problems, especially the "1r". I attached a copy of a screenshot to show you what I'm talking about.
How can I fix this? You'll see that there are 4 "N"s on the left side below the table.
Bookmarks