Add Markers to Filtered Data

[ChuckDrago]

Hi everyone,

I want to query a database via AutoFilter and add to each filtered record the acronym "WIP" on their rightmost column ("N"), so as to indicate that the item in question is under revision. The filtering macro is working as intended. The addition of the "WIP" marker is my stumbling block.

Will appreciate your suggestions, as usual.

Thanks,

Chuck

 

[AlphaFrog]

Maybe something like this after you have filtered.

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> Lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row<br>    Range("N2:N" & Lastrow).SpecialCells(xlCellTypeVisible).Value = "WIP"</FONT>

 

[ChuckDrago]

Alpha,

To expedite matters, I just copied your code verbatim after the filter line. The filtered records had the WIP marker written, but for some reason that escapes me, the lastrow number was larger than the last filtered row, therefore a large numbwer of WIP's were inserted in non-filtered records. Some referencial data:
The filtered records are 9, with aleatory row numbers between 17 and 383 (first and last, respectively). The calculated lastrow reports 568, so I get the markers properly inserted for the filtered records, but all of the ones with row numbers between 383 and 568 also get their WIP.

Probably a little adjustment, but I fail to see it.

Chuck

 

[AlphaFrog]

If you know the filtered range is N17 to N383 then you wouldn't need to determine the Lastrow. Just use...

<font face=Courier New>Range("N17:N383").SpecialCells(xlCellTypeVisible).Value = "WIP"</FONT>

 

[ChuckDrago]

Granted, I know the database depth. I just did the replacement and the portion that I cannot explain is why it writes the "WIP" to cells OUTSIDE the filtered range? I think the filter macro is doing something funny here. Thanks anyway.
Chuck