Set range to block of cells in Macro
 

I'm fairly new to Excel syntax and have written a macro to put a bunch of
data from various rows and columns on a worksheet into one block of data,
with the idea that I could name the block of data for grabbing it with an
outside application (Access).

I know where the corners of the block are, but I can't figure out the syntax
to use to define it. This is the closest I can get (RowCnt&ColCnt define the
upper left corner and EndRow&EndCol the lower right corner):

Set r1 = Range(Cells(RowCnt, ColCnt), Cells(EndRow, EndCol).End(xldown))

This works, but includes loads of empty rows below the block I'm interested
in; I'm presuming because of the "xldown" at the end, for which I haven't
found a list of optional substitutions for. The line gives an error without
the "xldown" part.

Can someone help me with the syntax? Also, if I do something like:
r1.name = "grabData"
Will that name be assigned to the range even after the macro ends?

Thanks!

Set range to block of cells in Macro
 

Sub demo()
r1 = 2
c1 = 2
r2 = 10
c2 = 10
Set r = Range(Cells(r1, c1), Cells(r2, c2))
MsgBox r.Address
End Sub

--
Gary''s Student - gsnu200722


"Karen" wrote:

I'm fairly new to Excel syntax and have written a macro to put a bunch of
data from various rows and columns on a worksheet into one block of data,
with the idea that I could name the block of data for grabbing it with an
outside application (Access).

I know where the corners of the block are, but I can't figure out the syntax
to use to define it. This is the closest I can get (RowCnt&ColCnt define the
upper left corner and EndRow&EndCol the lower right corner):

Set r1 = Range(Cells(RowCnt, ColCnt), Cells(EndRow, EndCol).End(xldown))

This works, but includes loads of empty rows below the block I'm interested
in; I'm presuming because of the "xldown" at the end, for which I haven't
found a list of optional substitutions for. The line gives an error without
the "xldown" part.

Can someone help me with the syntax? Also, if I do something like:
r1.name = "grabData"
Will that name be assigned to the range even after the macro ends?

Thanks!

Set range to block of cells in Macro
 

Thanks! That worked and I've also confirmed that the range name works too!
Marvelous!



"Karen" wrote:

I'm fairly new to Excel syntax and have written a macro to put a bunch of
data from various rows and columns on a worksheet into one block of data,
with the idea that I could name the block of data for grabbing it with an
outside application (Access).

I know where the corners of the block are, but I can't figure out the syntax
to use to define it. This is the closest I can get (RowCnt&ColCnt define the
upper left corner and EndRow&EndCol the lower right corner):

Set r1 = Range(Cells(RowCnt, ColCnt), Cells(EndRow, EndCol).End(xldown))

This works, but includes loads of empty rows below the block I'm interested
in; I'm presuming because of the "xldown" at the end, for which I haven't
found a list of optional substitutions for. The line gives an error without
the "xldown" part.

Can someone help me with the syntax? Also, if I do something like:
r1.name = "grabData"
Will that name be assigned to the range even after the macro ends?

Thanks!