VBA: Column union not working

[dpatel20]

I am trying to export specific columns from a defined table to CSV. If I use the following only the first column is output:

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?

 

[pgc01]

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

Hi

rngToSave refers already to the 2 columns.

Please confirm

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]

Yes, it seems to - I get:

$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?

 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]

 

[pgc01]

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).

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]

Something like (not tested)

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]

Thanks, the 'Areas' was the bit that I needed. I wanted something slightly different - here is my 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]

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.