Excell - copying just non-blank cells (rows) - VBA

[Evening]

I have a data in array A1:A1000, where the data is in A,B,C,D E fields. Only some of the rows contain data, and it could be: or all fields are filled, or none of them.
I want to copy it in VBA A:A1000, to a new location A1002, but only rows with some data written in fields. All empty won't be copied, so I will get continous rows with data.
I select the array in VBA I still have some data beside a,b,c,d e columns, so i didn't want to use filter.

Copying data is like:

Sheets("sheet-1").Range("A1:A1000").Copy

but not sure how can i paste to A1002 only nonblank rows.
Any thoughts?

 

[xlbo]

Add an extra column that uses a formula like

=if(len(A2&B2&C2&D2&E2)=0,"EMPTY ROW","Data Somewhere")
then just filter on "Data Somewhere" and copy the whole lot

btw - this should really be posted in the VBA forum

with sheets("Sheet-1")
    .range("A1:[b]F[/b]1000").autofilter field:=6, criteria1:="=Data Somewhere")
    .copy destination:= .Range("A1002")
end with

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question