copy multiple columns to new sheet

[dappy]

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.

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

 

[Fluff]

Rater than posting code that doesn't do what you want, could you please explain what you want?

 

[dappy]

Good point silly me.

I have data in columns A to P on one sheet with some rows blank. I need to copy all the data except blank rows to another sheet.

Appreciate your help

 

[Fluff]

Doe the entire row need to be blank, for it not to copy, is is there a specific column we can look at.

 

[dappy]

Yes, rows are either completely blank or data in columns A to P. hope that's clear

 

[Fluff]

Do you want all the columns stacked underneath each other, in col A?

 

[Fluff]

If the answer to post#6 is yes, then try

Sub dappy()
   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) * 16, 1 To 1)
   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, 1) = ary(r, c)
            End If
         End If
      Next r
   Next c
   Sheets("sheet2").Range("A1").Resize(j).Value = Nary
End Sub

 

[dappy]

thank you but no, I just need all the cells copied that have data, to be duplicated on the new sheet minus the blank rows, is that possible?

 

[dappy]

Just to clarify, I have rows 1 to 20 with only 10 of those rows with data. I need to copy only the rows with data so on the new sheet I have 10 rows all with data. Hope this helps

Thanks again

 

[Fluff]

Is there any particular column that will only be blank if the rest of the row is blank?