VBA: Column union not working

dpatel20

New Member
Joined
Feb 3, 2014
Messages
11
I am trying to export specific columns from a defined table to CSV. If I use the following only the first column is output:

Code:
With sh.ListObjects("MasterData")        
     Set rngToSave = Application.Union(.ListColumns("FATHER'S MOBILE").DataBodyRange, .ListColumns("CENTRE").DataBodyRange)
End With

How do I get rngToSave to be multiple columns that are not necessarily contiguous?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
How do I get rngToSave to be multiple columns that are not necessarily contiguous?

Hi

rngToSave refers already to the 2 columns.

Please confirm

Code:
With sh.ListObjects("MasterData")        
     Set rngToSave = Application.Union(.ListColumns("FATHER'S MOBILE").DataBodyRange, .ListColumns("CENTRE").DataBodyRange)
End With
MsgBox rngToSave.Address

You did not post how you are exporting.
 

dpatel20

New Member
Joined
Feb 3, 2014
Messages
11
Yes, it seems to - I get:

Code:
$W$2:$W$152,$AB$2:$AB$152

This is the code to write to CSV but the column count is only one so it only ever outputs the first column. Is using column count wrong?

Code:
 Open myCSVFileName For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fNum"]#fNum[/URL] 
    
    For i = 1 To rngToSave.Rows.Count
        For j = 1 To rngToSave.Columns.Count
            cellValue = rngToSave.Cells(i, j).Value
                If j = rngToSave.Columns.Count Then
                    Write [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , cellValue
                Else
                    Write [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , cellValue,
                End If
        Next j
    Next i
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fNum"]#fNum[/URL]
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

You have define a multi-area range.
Your code is only looping through the cells of the first area (the default).

You have to loop through all the areas and then do your code you posted.

Try this to see the 2 areas (you can have many areas and each area can span several columns and rows).

Code:
Sub Test()
Dim sh As Worksheet
Dim rngToSave As Range
Dim lArea As Long

Set sh = ActiveSheet
With sh.ListObjects("MasterData")
     Set rngToSave = Application.Union(.ListColumns("FATHER'S MOBILE").DataBodyRange, .ListColumns("CENTRE").DataBodyRange)
End With

MsgBox "Whole range: " & rngToSave.Address
For lArea = 1 To rngToSave.Areas.Count
    MsgBox "Area " & lArea & ": " & rngToSave.Areas(lArea).Address
Next lArea

End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Something like (not tested)

Code:
For k = 1 To rngToSave.Areas.Count
    For i = 1 To rngToSave.Areas(k).Rows.Count
        For j = 1 To rngToSave.Areas(k).Columns.Count
            cellValue = rngToSave.Areas(k).Cells(i, j).Value
                If j = rngToSave.Areas(k).Columns.Count Then
                    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cellValue
                Else
                    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cellValue,
                End If
        Next j
    Next i
Next k
 

dpatel20

New Member
Joined
Feb 3, 2014
Messages
11
Thanks, the 'Areas' was the bit that I needed. I wanted something slightly different - here is my code:

Code:
    For i = 1 To rngToSave.Areas(1).Rows.Count                      'Cycle all rows. Assume all areas have the same num of rows (could find column with max rows)
        For k = 1 To rngToSave.Areas.Count                          'Cycle all areas
            For j = 1 To rngToSave.Areas(k).Columns.Count           'Cycle each column in current area
                cellValue = rngToSave.Areas(k).Cells(i, j).Value    'Value for current row and column in this area
                    ' Last column in last area...don't add the comma
                    If j = rngToSave.Areas(k).Columns.Count And k = rngToSave.Areas.Count Then
                        Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cellValue
                    Else
                        Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cellValue,
                    End If
            Next j
        Next k
    Next i
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
I'm glad it helped.

Notice that if you switch the first 2 lines, like I did in the code I posted, you do not have to assume anything about the number of rows.
The code will loop through all the areas and through all the cells in each area even if each area has a different number of rows and columns.
 

Forum statistics

Threads
1,136,323
Messages
5,675,091
Members
419,549
Latest member
EliteBeat

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