What does 'Set rng = Range("Database")' do?

[Guest]

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

 

[Norman Jones]

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("A1100")

 

[Guest]

Norman, thanks you shed some light on my problem. I still have a question or
two. You statedthat 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 ofalso 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

 

[JE McGimpsey]

I still have a question or two. You stated
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?

One way is to select a range, then type "Database" in the Name box on
the left side of the formula bar, then press Return.

You can do it in code with

Range("A1:A100").Name = "Database"

IIRC, Database is also a built-in name for the area associated with a
DataForm.

As to why your example didn't define it, who knows? All you said was
that you found it "on the internet". One can find al *kinds* of examples
on the internet.

Your suggestion of
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!

How will your range vary?

If you just want to get all the contiguous data around a particular cell
(i.e., all data not separated from the cell by a blank vertical or
horizontal range), use the .CurrentRegion property of the Range object

Set rng = Range("A1").CurrentRegion

 

[Dave Peterson]

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(xlcelltypelastusedcell))
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(xlcelltypelastusedcell))
end with

 

[Guest]

Thanks to all, you answered my questions and I solved my problem!

Tom