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

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Hope this isn't too stupid a question

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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### erik.van.geit

##### MrExcel MVP
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
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
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

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

stil expecting other viewpoints ...

#### hatman

##### Well-known Member
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
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
Hatman - very helpful response, thanks 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
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
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
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.

1,102,783
Messages
5,488,855
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...