Apply VBA code to filtered rows

**jaff84** · 06-01-2012, 07:53 AM

Hi all,

As you can see here ( https://docs.google.com/spreadsheet/...1E&pli=1#gid=0 ), I have a spreadsheet that was filtered manually, and now I want to apply some VBA code I made, only to the filtered rows, presented in the link. How can I do this?

By the way, the code goes like this (the range present on the code is an old one, I want to change that for the filtered rows on the previous link):

Sub SortRows()
    Dim RgToSort As Range
    Dim RgRow As Range
    Application.ScreenUpdating = False
    
    Set RgToSort = Range(Range("B1:C42"), Range("B1:C42").End(xlDown))
    
    For Each RgRow In RgToSort.Rows
        RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
    Next
    Application.ScreenUpdating = True
End Sub

Thanks in advance,
João Fernandes

**Richard Buttrey** · 06-01-2012, 08:36 AM

Hi,

A brief test I've just done shows that if you sort a filtered range, only the visible rows have been sorted after you remove the filter. I was somewhat surprised but there you go.

Hence just use the standard VBA sort syntax that you showed in your example.

**jaff84** · 06-01-2012, 09:47 AM

Thanks for your quick and useful reply, Richard. Ok, I got it. I've just tested that code again, and in order to sort all the rows I wanted, I had to change the range to "B1:C61", to include all the rows I wanted in that Google Doc spreadsheet. One of the doubts I had was how to refer to the range (using the unfiltered cell references vs. filtered ones). I'm using the unfiltered ones, like in the example, and it's working smooth.

Regards,
João Fernandes

**Richard Buttrey** · 06-01-2012, 11:21 AM

Hi,

As you've discovered it's working OK anyway. But for future reference if you ever want to address just the filtered rows in a range of data, e.g. for copying, then you could use the following syntax.

Range("MyRange").SpecialCells(xlCellTypeVisible).Copy

Regards

**jaff84** · 06-01-2012, 12:38 PM

Hi,

As you've discovered it's working OK anyway. But for future reference if you ever want to address just the filtered rows in a range of data, e.g. for copying, then you could use the following syntax.

Range("MyRange").SpecialCells(xlCellTypeVisible).Copy

Regards

Thank you, that will make it general, just as I wanted, since I'll need to use it in different ranges. I'll try that.

Thanks again,
João