VBA Loop Through Columns

Question

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

SJR · Accepted Answer · 2017-05-19 15:09:34Z

2

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

answered May 19, 2017 at 15:09

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Loop Through Columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related