Cells(1,1) vs Range("A1")

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hope this isn't too stupid a question :confused:

Looking at the typical code of the guru's I get the impression that clever folk favour cells(1,1) method over Range("A1") method.

Just wondering what the advantages of the one method is over the other? I mean I can understand that for looping purposes (For i = 1 to 100) etc. it may be easier to construct.

Any other distinct advantages?

Best regards
Jon
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

first reason could be "micro-speed-optimization"
Code:
Sub test()
Dim starttime As Double
Dim s As String
Dim i As Long

starttime = Timer

    For i = 1 To 10 ^ 5
    s = Range("A1").Value
    's = Cells(1, 1).Value
    Next i
    
MsgBox Timer - starttime

End Sub
when you disable the first "s = ..." line and enable the second, you will notice that the second is quite faster

kind regards,
Erik
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Aah I see, thanks Erik. I suppose it's always good to know that you're working to optimum efficiency however slight it may be :)

I suppose the same principle applies with regards to Range("A65536").End(xlUp) vs. Cells(Rows.Count,1).End(xlUp) too then... Because when would Rows.Count not equal 65536?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
wait a moment...
my reply wasn't possibly the only answer!
perhaps a guru will tell more about it

as far as I understand this would be the logic:
Excel and VBA, computers in general like "numbers" not "strings"
I can imagine VBA performing some (luckily a quickworking) lookuptask to check out what "A" means in that context Range("A1")
I can imagine that the lookuptable is nothing more than the "default-names-table" (why else would "A1" be in the namebox?)
which explains the difference in speed

another obvious answer
Range("A1:D5") is easier to write than
Cells(1, 1).Resize(5, 4)

stil expecting other viewpoints ...
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Personally, I use both syntaxes, based on my application. I find the Range() method to provide better code readability, but it has some shortcomings...

If I am interacting with a single fixed cell:
Code:
 a = Range("BA285").value
If I am parsing a column of information:

Code:
For Cnt = 1 to 100
    range("BA" & cnt).value = Cnt
Next Cnt
If I am parsing rows and columns:

Code:
For Cnt_Row = 1 to 100
    For Cnt_Col = 1 to 100
        cells(Cnt_Row,Cnt_Col).value = "Row " & Cnt_Row & ", Column " & Cnt_Col
    Next Cnt_Col
Next Cnt_Row
How else would you parse columns using Range()? Up to Column Z, you can use the Chr() function:


Code:
For Cnt_Row = 1 to 100
    For Cnt_Col = 65 to 90
        cells(Cnt_Row,chr(Cnt_Col)).value = chr(Cnt_Col) & Cnt_Row
    Next Cnt_Col
Next Cnt_Row
But this breaks down beyond column Z, and you would need something like this to get there:

Code:
For Cnt_Row = 1 to 100
    For Cnt_Col = 1 to 256
        cells(Cnt_Row,alpha(Cnt_Col)).value = alpha(Cnt_Col) & Cnt_Row
    Next Cnt_Col
Next Cnt_Row

Function Alpha(num As Integer) As String

    b = Int(num / 26)
    
    a = num Mod 26
    
    If b = 0 Or (a = 0 And b = 1) Then
    
        Alpha = Chr(num + 64)
        
    Else
    
        If a = 0 Then
        
            Alpha = Chr(b + 63) & "Z"
        
        Else
            
            Alpha = Chr(b + 64) & Chr(a + 64)
            
        End If
        
    End If
    
End Function
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
I suppose the same principle applies with regards to Range("A65536").End(xlUp) vs. Cells(Rows.Count,1).End(xlUp) too then... Because when would Rows.Count not equal 65536?
If using an early version of Excel which only has about ~16000 rows, or the newest version of Excel which has ~1,000,000 rows...
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hatman - very helpful response, thanks (y) Certainly provides some good reasoning for using a combination of both. :)

ExcelChampion & Erik - couple of good points there too! Thanks :)

Cheers
Jon
 

macleanb

Well-known Member
Joined
Dec 10, 2004
Messages
715
Also, for completeness dont forget the "old" [A1] notation - which is slower still!

However that said, in my experience I would never use either range() or "[]" inside a loop, so its not an issue. I did briefly use the range("A" & counter ) approach, but I stopped (not for performance reasons)

I still do use the [a1] notation though as I find it very readable and kinda reminds me this is a constant address (and old habbits die hard)

Seems like cells is ~10% faster than range(), and
#VALUE!
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
For my money I have come to prefer loading a range into a variant array via:
Code:
Sub Test()
Dim vArr As Variant
Dim lIndx As Long
vArr = Excel.ActiveSheet.UsedRange
    For lIndx = 1 To 3
        vArr(lIndx, lIndx) = "Something New"
    Next lIndx
Excel.ActiveSheet.UsedRange = vArr
End Sub
Why? Because if something goes wrong during execution and the program aborts... The workbook is still in it's original state ;)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Mmmm, I'm gonna have to look into Variant later today, can't say I know what it's all about and I'm not entirely sure what the intended use of that code is. Are you changing the UsedRange?

I'll have a read on the topic later this p.m. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,748
Messages
5,482,616
Members
407,354
Latest member
Calvince

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top