copy multiple columns to new sheet

dappy

Board Regular
Joined
Apr 23, 2018
Messages
109
Office Version
  1. 2013
Platform
  1. Windows
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.

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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,811
Office Version
  1. 365
Platform
  1. Windows
Rater than posting code that doesn't do what you want, could you please explain what you want?
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
109
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,811
Office Version
  1. 365
Platform
  1. Windows
Doe the entire row need to be blank, for it not to copy, is is there a specific column we can look at.
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
109
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,811
Office Version
  1. 365
Platform
  1. Windows
Do you want all the columns stacked underneath each other, in col A?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,811
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If the answer to post#6 is yes, then try
VBA Code:
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

Board Regular
Joined
Apr 23, 2018
Messages
109
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Apr 23, 2018
Messages
109
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,811
Office Version
  1. 365
Platform
  1. Windows
Is there any particular column that will only be blank if the rest of the row is blank?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,077
Messages
5,639,934
Members
417,119
Latest member
adityaj252

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top