Avoiding select while referring to column by number

Asked Aug 25 '21 at 18:39

Active Aug 25 '21 at 18:39

Viewed 15 times

To start, I am a relative novice at VBA writing (used to using macro record).

I am trying to avoid using Select but only way I know how to loop through columns is using a number reference ... why I am using Cells. But, can't seem to remove the select if using Cells.

Current code (works but trying to remove the select step):

For i = 1 To 300

Dim ValCol As Long
ValCol = 9 + i

    Sheet19.Select
    Range(Cells(2, ValCol), Cells(23, ValCol)).Copy
    
    'lRow = Sheet25.Cells(Sheet25.Rows.count, "A").End(xlUp).Row + 1
    Sheet25.Range("J" & lRow & ":J" & lRow + 21).PasteSpecial Paste:=xlPasteValues

 Application.CutCopyMode = False

Next

edited Aug 25 '21 at 18:39

Scott Craner

148,073
10
49
81

asked Aug 25 '21 at 18:39

Crystal

`Sheet19.Range(Sheet19.Cells(2, ValCol), Sheet19.Cells(23, ValCol)).Copy` – Scott Craner Aug 25 '21 at 18:41
Also directly assigning the values will be quicker: `Sheet25.Range("J" & lRow & ":J" & lRow + 21).Value = Sheet19.Range(Sheet19.Cells(2, ValCol), Sheet19.Cells(23, ValCol)).Value` It avoids the clipboard. – Scott Craner Aug 25 '21 at 18:43
Super helpful, thank you! Works much faster – Crystal Aug 25 '21 at 20:05

Avoiding select while referring to column by number

0 Answers0