Difference between Columns("A").Select and Columns("A:A").Select

Question

I understand that Columns("A:B").Select is used to select both column A and B but I wonder if there's a difference between

Columns("A").Select
Columns("A:A").Select

I have tested both in my code and it looks the same.

Bonus question: Is there a difference in term of compilation time/run time between:

Columns("A").Select
Range("A1:A10000").Select

Thanks and have a good day !

compilation time or run time? compile time no would be my belief. — QHarr, Aug 10 '18 at 16:17
It's not my code, I have to translate VBA to another language! And how about run time ? — Hotbready, Aug 10 '18 at 16:17
@urdearboy I believe he is using .Select to determine the differences in the reference; I doubt he is actually going to use it in his code. — jcrizk, Aug 10 '18 at 16:21
Please see [how to avoid using select in excel vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) before you continue writing any more code. — Profex, Aug 10 '18 at 16:26
You're asking whether there's a difference in runtime, why don't you *test* it? I'm not sure how you'd test the compilation, though. — David Zemens, Aug 10 '18 at 16:32
@Marcucciboy2 - might hard selecting get you banned from SO? — SJR, Aug 10 '18 at 16:33

urdearboy · Answer 1 · 2018-08-10T17:00:27.023

2

Code to test time of various selection options:

Option Explicit

Sub SelectTime()

Dim LRow As Long
LRow = Range("C" & Rows.Count).End(xlUp).Offset(1).Row

Dim t
t = timer

'Range("A1").Select
Range("A:A").Select

Range("C" & LRow) = timer - t

End Sub

Output of 31 iterations of selecting 1 cell compared to selecting an entire column. Results did not change with or without cell content

edited Aug 10 '18 at 17:00

answered Aug 10 '18 at 16:33

urdearboy

14,439
5
28
58

1

Does the contents of the cells have any bearing on time taken to select? I can't imagine it does – Marcucciboy2 Aug 10 '18 at 16:35
3

I was going to say that you had a very slow computer, but I think the issue here is the time it takes to ouput the result and of course the fact that you never reset the timer. – Profex Aug 10 '18 at 16:38
Since you do not reset the timer, the differences are nearly identical which would indicate that there is no difference. – Scott Craner Aug 10 '18 at 16:42
1

I'm repeating this with a reset timer and assinging the `Timer-t` to a variable before it's written to the cell. There is literally no difference on blank cells, values are either 0.003906 or 0.0 across the entire output table. Even if all cells contain values. – David Zemens Aug 10 '18 at 16:47
I dont get what you mean @Scott. The question was `does it take longer to select more`, not `what is the exact time`. So, outputting the result should be constant, therefore **the trend should remain**. Or is this logic wrong? – urdearboy Aug 10 '18 at 16:48
1

The problem is that you're subtracting `t` (the *initial* time) from `Timer` for each row. So, `Timer` value changes but `t` does not. You need to set a new `t` value for each row. – David Zemens Aug 10 '18 at 16:51
3

You never re-start the timer. Every time you output the interval it takes a **whole lot** more time then actually selecting the cells – Profex Aug 10 '18 at 16:53
Yup I see now. Update @Profex don't know why I was thinking initial logic was ok - – urdearboy Aug 10 '18 at 17:02

David Zemens · Answer 2 · 2018-08-10T17:29:57.047

I've repeated urdearboy's test, but ensured to reset the timer for each row. I also ran it 100 times.

Values for ALL tests are either 0 or 0.003906.

Option Explicit

Sub SelectTime()

Dim t, v, i As Long

For i = 99 To 0 Step -1

t = Timer: Range("A:A").Select: v = Timer - t: Range("C2").Offset(, i) = v
t = Timer: Range("A1:A10").Select: v = Timer - t: Range("C3").Offset(, i) = v
t = Timer: Range("A1:A100").Select: v = Timer - t: Range("C4").Offset(, i) = v
t = Timer: Range("A1:A1000").Select: v = Timer - t: Range("C5").Offset(, i) = v
t = Timer: Range("A1:A10000").Select: v = Timer - t: Range("C6").Offset(, i) = v
t = Timer: Range("A1:A100000").Select: v = Timer - t: Range("C7").Offset(, i) = v
t = Timer: Range("A1:A1000000").Select: v = Timer - t: Range("C8").Offset(, i) = v

Next
End Sub

The average runtime for each of the 10 cases, across 100 tests each, there is virtually no difference for a non-empty range (my entire Column A populated with values).

The average result for each set of rows, across 100 tests is virtually the same, and the difference between the highest and lowest average is less than 1/1000th of a second.

More precise measurements:

You can measure elapsed time more precisely with a WinAPI call.

Option Explicit
Declare Function GetTickCount Lib "kernel32" () As Long

Sub SelectTime()

Dim t, v, i As Long
Application.Calculation = xlCalculationManual
For i = 499 To 0 Step -1

t = GetTickCount: Range("A:A").Select: v = GetTickCount - t: Range("C2").Offset(, i) = v
t = GetTickCount: Range("A1:A10").Select: v = GetTickCount - t: Range("C3").Offset(, i) = v
t = GetTickCount: Range("A1:A100").Select: v = GetTickCount - t: Range("C4").Offset(, i) = v
t = GetTickCount: Range("A1:A1000").Select: v = GetTickCount - t: Range("C5").Offset(, i) = v
t = GetTickCount: Range("A1:A10000").Select: v = GetTickCount - t: Range("C6").Offset(, i) = v
t = GetTickCount: Range("A1:A100000").Select: v = GetTickCount - t: Range("C7").Offset(, i) = v
t = GetTickCount: Range("A1:A1000000").Select: v = GetTickCount - t: Range("C8").Offset(, i) = v

Next
Application.Calculation = xlCalculationAutomatic
End Sub

Results across 500 tests for each selection are have a range of .852 milliseconds, with a min of .936 and a max of 1.788. The distribution doesn't seem dependent on the number of rows in the selection.

I'd just like to note that 0.003906 is the smallest time that can be registered. You really need to run it 100-1000 times to be able to compare any real difference (of which there still is none). — Profex, Aug 10 '18 at 17:14
Thanks for the info; I'll have to remember that if I need it in the future. I usually just run the code 10,000 times or whatever gives me nice numbers in the 1-10s range. — Profex, Aug 10 '18 at 17:22
@Profex `Declare Function GetTickCount Lib "kernel32" () As Long` to the millisecond. There still appears to be a limit at least as far as this is concerned, as I'm getting all values of 15, 16, or 0 milliseconds. Across 500 tests there's a range of .936 to 1.788 milliseconds. Doesn't appear to be related to the size of the selection :) — David Zemens, Aug 10 '18 at 17:26

score 0 · Answer 3 · answered Aug 10 '18 at 16:24

0

In this case:

Columns("A").Select
Columns("A:A").Select

There is no difference. You are selecting the same range.

In this case:

Columns("A").Select
Columns("A1:A10000").Select

Depends of the bunch of information have in these range. The more information has a range, more run time needed.

answered Aug 10 '18 at 16:24

Kauê Delmonte

38
7

I don't think that is true...about it taking more time for more information – Profex Aug 10 '18 at 16:25
Try to copy entire blank sheet to another. Now, try to copy a sheet with a bunch of data and paste to another. – Kauê Delmonte Aug 10 '18 at 16:27
Let's say there's only value from `Rows("A1:A75000")` – Hotbready Aug 10 '18 at 16:27
1

Can you prove your assertion that it takes more time to `Select` a range based on the contents of that range? Of course it takes more time to process or transform a range full of data, but that was not the OP's question. – David Zemens Aug 10 '18 at 16:29
If you select a range with some data and look at excel status bar you'll see there are a abstract of this range selected. If your application.calculation is setted to automatic, your excel application will do some calculations involving this selected range. That is, whenever you select a range, excel will execute some back calculations using the selected range data. – Kauê Delmonte Aug 10 '18 at 16:32
1

That's fair. I'd suggest adding that note to your answer above. But I think you're still missing the point of OP's question, which is: "Is there a run- or compile-time difference between `Columns("A")` and `Columns("A:A")` when used to identify a range object?" IOW, the `Select` is not actually relevant :) – David Zemens Aug 10 '18 at 16:34
Thank you for you answer and suggestion! That is, selected is no actually relevant, selecting a entire column or only a few cells with the information you want to work with, in the end, the time spent to it will be irrelevant. – Kauê Delmonte Aug 10 '18 at 16:36

Profex · Answer 4 · 2018-08-10T17:18:19.187

What is the point to this question? Becasue the answer is NO, there is no difference in selecting 1 cell vs 1,000,000 cells.

This is besides that fact that if you are using .Select for your code, you are doing it wrong.

The average time for selecting 2000 (2x1000) times a single cell ("A1"/B1") was between 1.793s and 2.039s.

The average time for selecting 2000 (2x1000) times an entire column ("A"/B") was between 1.816s and 1.914s.

This was only from running my timer 4 times...If I continue I will see very similar results with a little bit more variation. But in the end, the time taken will overlap. It is only dependent or how busy the CPU is doing other things, like updating the mouse pointer when you move it or running services (eg. antivirus) in the backgroud.

Here, try it out for yourself. Just make sure to run the test multiple times, because I think you'll notice that it varies on what other processes are using up the CPU at the time.

Public Sub TimeStamp(Optional Prompt As String, Optional StartTime As Boolean)
Static s_fTimer As Single, s_fIntervalTimer As Single
Dim fCurrTime As Single
    fCurrTime = Timer
    If StartTime Then
        s_fTimer = fCurrTime
        s_fIntervalTimer = fCurrTime
    End If
    If Prompt <> vbNullString Then Prompt = " - " & Prompt
    Debug.Print Format((fCurrTime - s_fTimer), "0.000s") & Format((fCurrTime - s_fIntervalTimer), "(0.000s)") & Prompt
    s_fIntervalTimer = fCurrTime
End Sub

Sub Test()
Const max As Long = 1000
Dim i As Long
    TimeStamp "Start", True
    For i = 1 To max
        Columns("A").Select
        Columns("B").Select
    Next
    TimeStamp "A"
    For i = 1 To max
        Columns("A:A").Select
        Columns("B:B").Select
    Next
    TimeStamp "A:A"
    For i = 1 To max
        Range("A1:A100000").Select
        Range("B1:B100000").Select
    Next
    TimeStamp "A100000"
    For i = 1 To max
        Range("A1").Select
        Range("B1").Select
    Next
    TimeStamp "A1"
End Sub

Difference between Columns("A").Select and Columns("A:A").Select

4 Answers4

More precise measurements: