Selecting Columns via Header Name - Multiple

[goodysgotacuda]

Hello!

I am trying to get the following code to work as I would like it to, but am getting hung up. I have ~29 columns of data that come back to be, the columns can be scrambled in and order. What I would like to do is take Latitude, Longitude, and Altitude, select them, copy and paste to another sheet.

So far I can get it to either find and select all three headers only, or it will select more than one all the way down if they are neighbors. So the following code as shown will select C1:D1 to the last row of data, I'd like to do include F1:xlDown in that as well.

Dim GPS1 As Range, GPS2 As Range
Dim GPS3 As Range, All As Range

  Set GPS1 = Range("A1:AZ1").Find("Latitude")
  Set GPS2 = Range("A1:AZ1").Find("Longitude")
  Set GPS3 = Range("A1:AZ1").Find("Altitude)")
  
   Set All = Union(GPS1, GPS2, GPS3)
   Range(All, All.End(xlDown)).Select

Any help? Thanks!

	A	B	C	D	E	F
1	RPM	Torque	Latitude	Longitude	MPH	Altitude
2	1000	900	33	92	25	1000

<tbody>
</tbody>

 

[VBA Geek]

sub selectSpec
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Union(Range("a1:az1").Find("Latitude").Resize(lrow), Range("a1:az1").Find("Longitude").Resize(lrow), Range("a1:az1").Find("Altitude").Resize(lrow)).Select
end sub

 

[JoeMo]

If the last row in col A isn't as large as the last row in your Latitude, Longitude, and Altitude columns then try this::

Sub Select3Cols()
Dim GPS1 As Range, GPS2 As Range
Dim GPS3 As Range, All As Range
Dim lR As Long, Ar As Range

  Set GPS1 = Range(Range("A1:AZ1").Find("Latitude"), Range("A1:AZ1").Find("Latitude").End(xlDown))
  Set GPS2 = Range(Range("A1:AZ1").Find("Longitude"), Range("A1:AZ1").Find("Longitude").End(xlDown))
  Set GPS3 = Range(Range("A1:AZ1").Find("Altitude"), Range("A1:AZ1").Find("Altitude").End(xlDown))
  lR = WorksheetFunction.Max(GPS1.Rows.Count, GPS2.Rows.Count, GPS3.Rows.Count)
  Set GPS1 = GPS1.Resize(lR, 1)
  Set GPS2 = GPS2.Resize(lR, 1)
  Set GPS3 = GPS3.Resize(lR, 1)
  Set All = Union(GPS1, GPS2, GPS3)
  All.Select
End Sub