Reputation: 95
I am trying to write a macro that will select the column with "Est #" in the first row and paste the entire column in a new column.
I know how to loop through rows:
Sub test()
Dim i As Integer
For i = 1 To 100
If Range("C" & i) = "All values USD Millions." Then
Range("C" & i & ":H" & i).Delete
Else
Range("A3").Select
End If
Next
End Sub
The problem is that columns have letters, not numbers, so I am unsure how I can loop through the first 30 columns and paste the column with "Est #" in the first row into Range("CA:CA").
Upvotes: 1
Views: 10259
Reputation: 23081
You could do it thus, although Find is more efficient
Sub test()
Dim c As Long
For c = 1 To 30
If Cells(1, c).Value = "Est #" Then
Cells(1, c).EntireColumn.Copy Cells(1, "CA")
End If
Next
End Sub
Here is the Find method
Sub test()
Dim rFind As Range
Set rFind = Range("A1").Resize(, 30).Find(What:="Est #", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
rFind.EntireColumn.Copy Cells(1, "CA")
End If
End Sub
Upvotes: 2