Just another way.

Most of the time, I can pick out a column that always has entries (some
index/key perhaps). And I can pick out a row that always has entries
(headers???).

Then I can do things like:

dim myRng as range
dim LastRow as long
dim LastCol as long

with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set myRng = .range("a1",.cells(lastrow,lastcol))
end with

====
Another option if you can trust to use xl's lastused cell:

dim myRng as range
with worksheets("sheet1")
set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell))
end with

But excel keeps pretty strict track of what you've ever used. If you put a
value in x99 and then clear contents, you can see what I mean if you hit
ctrl-End. You'll be taken to whatever excel thinks is the lastusedcell--not
always what you would have thought.

Sometimes just using .usedrange in code will reset that lastusedcell (sometimes
not).


dim myRng as range
with worksheets("sheet1")
set myrng = .usedrange 'try to reset usedrange
set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell))
end with



Tom wrote:

Norman, thanks you shed some light on my problem. I still have a question or
two. You stated
If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered.
that makes perfect sense but how do I "create a range named Database" and
why don't I see anything named Database on the example?

Your suggestion of
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:D100")
also makes perfect sense however, both the number of columns and rows will
vary from time to time. How can I be sure I select all the data? Sorry to
bother everyone with such a simple question but I have wasted an entire day
on this and I am stumped!

Thanks,
Tom

"Norman Jones" wrote:

Hi Tom,

Set rng = Range("Database")'

sets the range object rng to a worksheet range named Database.

If you do not have a range named Database on your sheet you will get an
error message of the type that you have encountered.

The solution is either to name the requisite worksheet range in Excel or,
alternatively, define the range directly, e,g,

Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:D100")

---
Regards,
Norman



"Tom" wrote in message
...
I'm a professional programmer but new to programming Excel. I recently
found
an example on the internet that has the line 'Set rng = Range("Database")'
in
it. Everything works fine in the example. When I modify the code and try
to
use it on my spreadsheet I get an error on the 'Set rng =
Range("Database")'
line. The error is: Run-time error '1004': Method 'Range' of object
'_Global' failed. Any ideas as to why it works on the example but errors
on
my spreadsheet?

Thanks,
Tom




--

Dave Peterson