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!

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!

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!