I've filtered a list on a spreadsheet and I'd like to create a sub which can loop through each of the resulting rows and convert the data into a string - strResult=cells(strCounter, 1) sort of thing. I can create the loop but I can't get it to isolate only the resulting rows.
Any ideas?
If you do not need the filtered data you can simply invert the filter and with the macro select the all cells and delete when that is complete you data is the only stuff left.
another way is to cheat and copy the entire filtered data set and past it on a new working sheet. I have used both methods and works fast and clean.
My reason for automating this is that I actually need to filter the list over 800 times through 30,000 rows of data. So - I can't disrupt the data as such.
At present I am using a copy.paste method but this is causing memory exception (Dr Watson) errors on our network - citrix server etc. etc. so I'm looking for alternatives to copy.paste and picking up variables is a nice snappy solution - if I can work out how to make the stupid thing work it's way down a filtered list.
Well I am not sure how to do as you ask. But another thought for you memory problems. You can also try to copy and paste special values if it would not kill you application. It might reduce the amound of data you are parsing through.
Other than that I have wondered about moving through a filtered list but have not learned how to do it myself. Perhaps you could build the filter into your macro.
Ie using case statements
for x = 1 to number_rows
test_value = cells(x,1)
select case test_value
case 1 to 10
copy cells(x,1).entire row
and paste them somewhere on another sheet or perform the task desired on the active row.
case 11 to 20
copy to another sheet or something.
...
end case
this is stylized not exact code but an idea
You would need to use something along the lines of;
Dim FltrRng As Range
Set FltrRng = Cells.SpecialCells(xlCellTypeVisible)
Dim c As Range
For Each c In FltrRng
'code to do something
Next
Don’t have an account yet? Register yourself now and be a part of our community!
