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):

Please Login or Register  to view this content.

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