Color Filtered Range

[MARKG]

Hello;

I have an autofilter on more than 10 columns of data. I don't have any problems writing code to filter different scenarios. My problem is figuring out what code will filter the data and only color the "result" data up to the last row of the result?

To get by, I have a haphazard macro setup that will filter data and color a set range. I did this to test my "sort by color" and that now works. However, the data will constantly fluctuate therefore my row count will never be the same and I'm left with a lot of blank rows that are colored. Thank-you for your assistance.

 

[Juan Pablo González]

This macro works, it gives the last used (And visible) row of column A. You can adjust it to suit your needs.

<pre>Sub Test()
MsgBox Range("A65536").End(xlUp).Row
End Sub</pre>

 

[MARKG]

Thank-you for the MsgBox - but that doesn't suit my needs. I probably didn't explain it clearly.

Here is my current code:
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter Field:=10, Criteria1:="<>*---*", Operator:=xlAnd
Selection.AutoFilter Field:=9, Criteria1:="~*---~*"
Rows("2:237").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

I wan't to eliminate the [Rows("2:237").Select]line as I will never know how many rows appear as a result of the filter. I'm trying to figure out some code that will take just the "result" rows and color them as indicated above. The msgbox actually counted all hidden rows. For instance my "result" rows were 4 but the actual hidden rows were 85, and your msgbox gave the result of 85 - which is not what I needed. Thank-you for your efforts, though.

 

[Juan Pablo González]

Actually no. The MsgBox should give you the last visible row after you make the Autofilter. I posted that only as an example of how you can get the last visible row, and then, you could create using a combination of Cells and Range the "resulting" range only. Another option is to use the SpecialCells method, having it to work with Visible cells only.