Newbie Q: Range from two cells
 

Hi All:

I'm pretty familiar with Word VBA and am now blundering around Excel. A
few successes so far. I find the trickiest thing so far is adapting
myself from Word's range object to Excel's.

An elementary question. I have two variables, representing two cells in
the same column. What is the easy way to return a range = to all the
cells between (and including) the two? So far my untutored brain can
only think of a complicated statement involving offsets and resizes.


Oh.. if there are any traps-for-young-players a newbie should know, I'd
appreciate it.

Thanks for your time,
Garry

Newbie Q: Range from two cells
 

Assuming the two variables you are refering to are defined as ranges
then maybe like this:

Sub test1()
Dim Cell1 As Range
Dim Cell2 As Range
Dim myRange As Range

Set Cell1 = Range("A10")
Set Cell2 = Range("A20")


Set myRange = Range(Cell1.Address & ":" & Cell2.Address)
Debug.Print myRange.Address

End Sub

Otherwise if you have variables representing row numbers then:

Sub test2()
Dim sRow As Long
Dim eRow As Long
Dim myRange As Range

sRow = 10
eRow = 20

Set myRange = Range(Cells(sRow, "A"), Cells(eRow, "A"))
Debug.Print myRange.Address

End Sub

Hope this helps
Rowan

Garry wrote:
Hi All:

I'm pretty familiar with Word VBA and am now blundering around Excel. A
few successes so far. I find the trickiest thing so far is adapting
myself from Word's range object to Excel's.

An elementary question. I have two variables, representing two cells in
the same column. What is the easy way to return a range = to all the
cells between (and including) the two? So far my untutored brain can
only think of a complicated statement involving offsets and resizes.


Oh.. if there are any traps-for-young-players a newbie should know, I'd
appreciate it.

Thanks for your time,
Garry

Newbie Q: Range from two cells
 

Thanks for that Rowan. I'm a bit surprised that one converts things to
a string then back to a range. Is this a common technique in Excel?

Again, thanks for your response,
Garry

Newbie Q: Range from two cells
 

Thanks for that Rowan. I'm a bit surprised that one converts things to
a string then back to a range. Is this a common technique in Excel?

Again, thanks for your response,
Garry

Newbie Q: Range from two cells
 

Hi Garry

If you refer to a range using the A1 notation then you would use a
string to define the address of that range e.g.
Range("A1").font.bold = true
So the methods I used to accomplish your requirements were the most
intuitive way to do this for me. There are other ways of doing it e.g.

Sub test3()
Dim CellA As Range
Dim CellB As Range
Dim myRange As Range

Set CellA = Range("A10")
Set CellB = Range("A20")

Set myRange = CellA.Resize(CellB.Row - CellA.Row + 1, 1)
Debug.Print myRange.Address

End Sub

More on working with ranges at:
http://j-walk.com/ss/excel/tips/tip20.htm

Regards
Rowan

Garry wrote:
Thanks for that Rowan. I'm a bit surprised that one converts things to
a string then back to a range. Is this a common technique in Excel?

Again, thanks for your response,
Garry

Newbie Q: Range from two cells
 

you don't need test strings or addresses.

Sub test()
Dim cell_A As Range
Dim cell_B As Range
Dim target As Range

Set cell_A = Cells(4, 5)
Set cell_B = Cells(12, 5)

Set target = Range(cell_A, cell_B)

target.Select

End Sub

"Garry" wrote:

Hi All:

I'm pretty familiar with Word VBA and am now blundering around Excel. A
few successes so far. I find the trickiest thing so far is adapting
myself from Word's range object to Excel's.

An elementary question. I have two variables, representing two cells in
the same column. What is the easy way to return a range = to all the
cells between (and including) the two? So far my untutored brain can
only think of a complicated statement involving offsets and resizes.


Oh.. if there are any traps-for-young-players a newbie should know, I'd
appreciate it.

Thanks for your time,
Garry