Save row to array

Tough nut to crack for me. I'm writing a small marcro that loops through every row on a sheet. If the row meets a certain condition, (part of it) should be transferred to another sheet.

The way I do it is now is no good. Every time I need to copy a row from the source sheet, I copy it, swap to the target sheet and paste it there. Then I swap back to the source sheet, ...
It's a spectacular show but it doesn't look too efficient

So I was thinking of making a one dimensional array and populate it with rows (colums A-F). I can get a cell in an array but how do I get part of a row in it? Every time I think something is impossible I'm mistaken so I'm hoping this is one of those times

Cheers,
Jeroen

The fastest way would probably be to use a filter and then just copy the visible rows in one go. We'd need to know more detail to write the code though.
If you really want to loop, you can use an array of arrays and then write that to the worksheet at the end.

Filters won't work with colours in 2003, so you'll need a loop after all.
Try this:

Please Login or Register  to view this content.

Probably - I've just posted some code that should work in either.

Try replacing all instances of .Value with .Value2
You will need to format the destination cells as dates though.

Applying this to a cell should work too (credits go to you though as I found it in a search). CDate(txtDate.Value). However, that would probably require me to loop through the array and apply this to every date.

So I'm going to try your suggestion first

edit: Yep, value2 seems to copy date and currency without the formatting. It's either reformatting the specific array index which holds the date/currency to the correct local (if you use value) or formatting the destinations cells to date (if you use value2).

Just 4 fun

Please Login or Register  to view this content.