Loop through columns names : For variable = A to Z

Question

is it possible to loop on column letters?

For col = A to Z
   column(col:col).select
   selection.copy
Next

Or am I oblige to use a function to transform letters into numbers?

what are you doing with the `selection.copy` once you finish the loop ? maybe it's not necessary ? can you post the rest of your code ? — Shai Rado, Nov 17 '16 at 14:28
Since column(:) returns a range, why would you need to iterate them manually? — Jaaz Cole, Nov 17 '16 at 14:53

Slai · Accepted Answer · 2016-11-17T15:21:56.957

1

To convert Formulas to Values:

Dim cols As Range, c As String
Set cols = UsedRange.Columns

For Each c In Split("N Q T")   ' add the rest of the column letters
    cols(c).Value = cols(c).Value ' or .Value2 if no dates or currencies in the range
Next

If it is every third column from column N, another approach can be:

Dim col As Range, i As Long
Set col = UsedRange.Columns("N")

For i = 1 To 17            ' to repeat 17 times
    col.Value = col.Value
    Set col = col.Offset(, 3)
Next

edited Nov 17 '16 at 15:21

answered Nov 17 '16 at 15:16

Slai

22,144
5
45
53

thank you, i'll test later, seems it's working but I have shorts deadline, I can't waste too much time on optimization. But thank you again for your help and your time =) – Verd'O Nov 17 '16 at 15:43

score 0 · Answer 2 · answered Nov 17 '16 at 14:19

0

No need to transform letters into numbers as you can pass letters to Columns and Cells:

Columns("B") 'the same as Columns(2)
Cells(1, "B") 'same as Cells(1, 2)

However looping through letters is kind of a hassle and you'd have to transform a number into a letter first, for example using Chr(64 + Num)

answered Nov 17 '16 at 14:19

arcadeprecinct

3,767
1
12
18

look my answer :) – Verd'O Nov 17 '16 at 14:24

Verd'O · Answer 3 · 2016-11-17T14:33:32.040

I don't know if it take a lot of calculating time but I actually have

Columns("N:N").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Columns("Q:Q").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Columns("T:T").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I have 17 time this group of rows, do you think it's a huge waste of time?

EDIT

answer with the link in the comment below, thanks to @Rdster

Yes...also, there's this - http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Rdster, Nov 17 '16 at 14:29

score 0 · Answer 4 · answered Nov 17 '16 at 14:46

I suggest one of these two solutions : 1- The Column A is the first column, and Z is the 26th column

'Declaration
Dim iFirstCol as integer
Dim iLastCol as integer
iFirstCol = 1 'the first column (A)
iLastCol = 26 'the last column (Z)

'Looping
FOR col = iFirstCol to iLastCol
    Columns(col:col).Select
    Selection.Copy
    '....
LOOP col

2- The ASCII values of the letters A to Z are 65 to 90.

'Declaration
Dim Cols as string

Your loop can be easily transformed into:

'Looping
FOR col = 65 to 90
    Cols=CHR(col) & ":" & CHR(col)
    Columns(Cols).Select
    Selection.Copy
'...
LOOP col

Hope this can help!

Loop through columns names : For variable = A to Z

4 Answers4