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!
|