Sounds like you have some help coming from William including code. Instead
of doing 5 things at once, lets wait and see if you've got the answer you
want from William. I didn't read his code so I cannot comment on it. I know
that I generally try to always refer to range names in my code because they
are flexible and easy to use. They are also easy to find and reset.
--
RMC,CPA


"Terry VanDuzee" wrote in message
...
It sounds like it could work, except (and I know I did not describe this
earlier), I have 29 sheets with different ranges used, and I need to be able
to pass a name or range to a sub that would be able to use the range as a
variable value.
Finding the actual used range can be placed into a sub and only the
worksheetname has to be fed into the sub (which Im doing using
Worksheet(name).index +1. I know I'll run into problems down the road when
it comes to my chartsheets but Ill work that out then).

Thank you so much
Terry V


"R. Choate" wrote in message
...
Well, when I do stuff like that, I would go to the workbook I'm interested
in and select the current region, then I name the range. After that, I
would
pass the range name to the macro as you describe. Does that sound like it
would work for you?
--
RMC,CPA


"Terry VanDuzee" wrote in message
...
Yes, it is a consistent block of data, only the range of cells used will
vary.

thank you
Terry V
"R. Choate" wrote in message
...
Would the used range be a range of contiguous populated cells, bound by
a
blank row and a blank column? In other words, would this be the current
region on a sheet if you were to click on one cell in the range?
--

RMC,CPA

"Terry VanDuzee" wrote in message
...
Hello
How can I get the usedrange from a sheet in a closed workbook?

This is how I am getting the values:

With ActiveSheet.Range(cellrange)
.FormulaArray = "='" & fpath & "\[" & fname & "]" _
& sname & "'!" & cellrange
End With

Where cellrange right now is a variable that contains a range that is
hardcoded. I want to be able to find the usedrange in the closed
workbook,
to pass it as a variable to this sub from the calling sub.


Thank you
Terry V