create excel file from uncontiginous date

[neveu]

hy forum,

would need your support with the following challenge:

i have raw data with several columns - which can also contain blank columns or uneeded data ; i would like to copy information from only several columns into a new excel file.

here is an example:

row data

col1 blankcol column2 blankcol column3

the layout in the excel i create should be

col1 column2 column3


i've tried using Union but i don't quite get the code to select the range retrieved with Union and copy it in the new excel file.

 

[venkat1926]

not clear why not delete the blank columns. perhaps a trivial solution. explain little more preferably with small extract of your sheet

 

[AlphaFrog]

As an example, this copies non contiguous columns from Sheet1 to Sheet2.

Sheets("Sheet1").Range("A:A, C:C, E:E").Copy _
    Destination:=Sheets("Sheet2").Range("A1")

 

[pgc01]

i've tried using Union but i don't quite get the code to select the range retrieved with Union and copy it in the new excel file.

Hi

No problem using Union either:

Dim r As Range
 
With Worksheets("Sheet1")
    Set r = Application.Union(.Columns(1), .Columns(3), .Columns(5))
End With
 
r.Copy Destination:=Worksheets("Sheet3").Range("J1")

 

[neveu]

hi all,

the issue with Union is that i;m using it in a loop that goes through 20 columns and if these have a "yes" asociated on another column, then the column will be added to my range

 

[pgc01]

hi all,

the issue with Union is that i;m using it in a loop that goes through 20 columns and if these have a "yes" asociated on another column, then the column will be added to my range

Hi

I don't see any problem in adding columns to the range.

For ex., using the code that I posted

With Worksheets("Sheet1")
    Set r = Application.Union(.Columns(1), .Columns(3), .Columns(5))
End With

if you want to add another column, you can use

With Worksheets("Sheet1")
    Set r = Application.Union(r, .Columns(10))
End With

You can add more columns like this in the loop.

If you have difficulties post the relevant part of the code you are using.

 

[neveu]

thanks for the support.

based on your inputs i managed to make it work
thanks