Hi folks,
I've had much help before copying columns to another sheet but sadly i'm back here again.
I have this, to copy from sheet1 to sheet2 ignoring the rows with spaces. but i only get what's in column A copied and nothing else? I'd sincerely appreciate any advice.
much thanks in advance and have a great weekend!
Carl
I've had much help before copying columns to another sheet but sadly i'm back here again.
I have this, to copy from sheet1 to sheet2 ignoring the rows with spaces. but i only get what's in column A copied and nothing else? I'd sincerely appreciate any advice.
VBA Code:
Dim ary As Variant, Nary As Variant
Dim R As Long, C As Long, j As Long
ary = Intersect(Sheets("sheet1").UsedRange, Sheets("sheet1").Range("A:P"))
ReDim Nary(1 To UBound(ary, 1) * 5)
For C = 1 To UBound(ary, 2)
For R = 1 To UBound(ary, 1)
If Not IsError(ary(R, C)) Then
If Not Len(ary(R, C)) = 0 Then
j = j + 1
Nary(j) = ary(R, C)
End If
End If
Next R
Next C
For R = 1 To j
Sheets("sheet2").Range("A1").Offset(R).Value = Nary(R)
Next R
much thanks in advance and have a great weekend!
Carl
Last edited by a moderator: