I was reading some VBA in a previous module somebody had written and came across something that confused me:
Sheets("Setup").Select
Range("Start").Select
ActiveCell.Offset(1, 0).Range("A1").Select
I was wondering how the ActiveCell.Offset(column,row).Range().Select line worked. In this case the "Start" range is a cell, A18, and the offset offsets it by one row, that much I get. But I'm not following how or what the Range("A1") is being inserted to do here.
Wouldn't
Sheets("Setup").Select
Range("Start").Select
ActiveCell.Offset(1, 0).Select
work just the same and be less confusing? Is there any reason the Range("A1") clause is inserted?
Thanks so much, and sorry for the beginner question.
1 Answer
Short answer
Yes in this particular case both do the same. Removing Range("A1") is fine.
Long Answer
This is due to the fact you are using ActiveCell in this line:
ActiveCell.Offset(1, 0).Range("A1").Select
The ActiveCell is the first cell within the range you have selected.
Consider the following macro:
Sub Macro1()
Debug.Print ActiveCell.Address
End Sub
Whatever range you select this will print the address of the white cell within the selection.
i.e.
The ActiveCell is $A$4
Example
Calling Offset(1,0) on a single cell will only offset that cell. So if we look at your original code:
Sub Macro2()
Sheets("Setup").Select
Range("Start").Select
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Let us assume that my previous selection shown (A2:B4) is the named range of "Start" and we can walk through exactly what is happening:
In this example
Range("Start").Selectwould select rangeA2:B4. Thus makingActiveCellequal toA2.Next we call
Offset(1,0)onActiveCellwhich is equivalent toRange("A2").Offset(1,0)putting us at rangeA3(1 row below A2)Now we call
.Range("A1")which is going to grab the first cell within the range. Since the current range is onlyA3,.Range("A1")gives usA3.Then of course
.Select()is still only selectingA3
When is .Range("A1") actually useful?
Consider the following example without any Range("A1") call:
Sub Macro3()
Sheets("Setup").Select
Range("Start").Select
Selection.Offset(1, 0).Select
End Sub
Since we have changed ActiveCell to Selection the Offset(1,0) will select the same dimension range as "Start" just offset-ed by 1 row.
i.e.:
If this is the range of "Start":
We run the example macro:
We have a new selection of the same dimension.
However if we change the example macro to include the Range("A1"):
Sub Macro4()
Sheets("Setup").Select
Range("Start").Select
Selection.Offset(1, 0).Range("A1").Select
End Sub
Only the first cell in the selection is now selected.