Select Multiple Table columns by header name (vba)

[sspatriots]

Can someone please give me an example of how to select random table columns based on header name? I tried using Array, but I don't think that is the right thing to use as they are random. I don't want to reference column numbers or letters. Thanks, SS

What I have below is where I am. I would rather select the range based on the Set Names I have. Also, what I originally used forced me to break it up into two defined Formatted Ranges, 1 & 2.

Dim ws As Worksheet
Dim tb As ListObject
Dim FormattedRange1 As Range
Dim FormattedRange2 As Range
Dim FormattedRange As Range
Dim My_Range1 As Range
Dim My_Range2 As Range
Dim My_Range3 As Range
Dim My_Range4 As Range
Dim My_Range5 As Range
Dim My_Range6 As Range
Dim My_Range7 As Range

    Application.CutCopyMode = False
    
Set ws = Sheets("Jobs")
Set tb = ws.ListObjects("G2JobList")
Set My_Range1 = Range("G2JobList[[Cab" & Chr(10) & "REQD Date]], G2JobList[[Cab" & Chr(10) & "RCVD Date]]")
Set My_Range2 = Range("G2JobList[[ENT" & Chr(10) & "REQD Date]], G2JobList[[ENT" & Chr(10) & "RCVD Date]]")
Set My_Range3 = Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]], G2JobList[[FXTR" & Chr(10) & "RCVD Date]]")
Set My_Range4 = Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]], G2JobList[[CONTR" & Chr(10) & "RCVD Date]]")
Set My_Range5 = Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]], G2JobList[[Door EQPT" & Chr(10) & "RCVD Date]]")
Set My_Range6 = Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]], G2JobList[[Wiring" & Chr(10) & "RCVD Date]]")
Set My_Range7 = Range("G2JobList[[Jack" & Chr(10) & "REQD Date]], G2JobList[[Jack" & Chr(10) & "RCVD Date]]")

''Define Formatted Ranges for Deletion before Re-Formatting (Too many ranges to be defined under just one name)
Set FormattedRange1 = Range("G2JobList[[Cab" & Chr(10) & "REQD Date]], G2JobList[[Cab" & Chr(10) & _
"RCVD Date]], G2JobList[[ENT" & Chr(10) & "REQD Date]], G2JobList[[ENT" & Chr(10) & _
"RCVD Date]], G2JobList[[FXTR" & Chr(10) & "REQD Date]], G2JobList[[FXTR" & Chr(10) & _
"RCVD Date]], G2JobList[[CONTR" & Chr(10) & "REQD Date]], G2JobList[[CONTR" & Chr(10) & _
"RCVD Date]]")

Set FormattedRange2 = Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]], G2JobList[[Door EQPT" & Chr(10) & _
"RCVD Date]], G2JobList[[Wiring" & Chr(10) & "REQD Date]], G2JobList[[Wiring" & Chr(10) & _
"RCVD Date]], G2JobList[[Jack" & Chr(10) & "REQD Date]], G2JobList[[Jack" & Chr(10) & _
"RCVD Date]]")

 

[Akuini]

To select multiple columns base on the headers:

Sub select_column()

Dim c As Range, x
With Sheets("Sheet1").ListObjects("Table1")

    For Each x In Array("id", "name", "phone") 'headers name
        If c Is Nothing Then
            Set c = .ListColumns(x).Range
        Else
            Set c = Union(c, .ListColumns(x).Range)
        End If
    Next
   
End With

c.Select

End Sub

 

[sspatriots]

Thank you. I will give this a try when I get back in the office on Monday. Steve

 

[Akuini]

OK.
And if you want to select only the data part (without the header) then use DataBodyRange instead of range, like this:

Sub select_column1()

Dim c As Range, x
With Sheets("Sheet1").ListObjects("Table1")

    For Each x In Array("id", "Name", "phone") 'headers name
        If c Is Nothing Then
            Set c = .ListColumns(x).DataBodyRange
        Else
            Set c = Union(c, .ListColumns(x).DataBodyRange)
        End If
    Next
    
End With

c.Select

End Sub

 

[sspatriots]

That did it what I needed for that piece of code. Thank you...

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[sanket6220]

I'm getting error in 3rd line as my sheet name is also Sheet1 only.

 

[Akuini]

I'm getting error in 3rd line as my sheet name is also Sheet1 only.

Welcome to the Forum
1. What's the error message?
2. What's the table name? Table1?