About Us

Hydra

Oh, of course. Now I get it.

"Patrick Molloy" wrote:

I'm lost with where you're going with this.

simply

1)
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With

2) Range("A1").Name = "something"

3) Range("A1:G10").Name = "something"

4) Cells(1,5).Name = "something"

in (1) the name to be used is in a cell eg

5) Range("A1:G10").Name = Range("h1").Value

"Hydra" wrote in message
...
What I finally came up with was :

ActiveCell.Range("A1:" & Lastaddress).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

But what I really need to use is something like your "end down" code to
set
the range for each column individually.

"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with

"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I
had
planned to put in code similar to yours. Thanks for saving me from
writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub

"Hydra" wrote in message
...
Sorry, Should say

Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying
to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the
rangenames
out
of th ecolumn headers, which may change.

"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and
column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is
copied
right
out of the help screen. This throws a syntax error message.

Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because
it
confused the variable with the column name and accused me of trying
to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned
properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????

"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA

"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a
table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Narrow Range based on Selection in Another Range	David	Excel Discussion (Misc queries)	3	July 1st 07 05:12 PM
Range(Selection, Selection.End(xlToRight)).Select	Dave Birley	Excel Programming	2	June 6th 07 04:53 PM
Identifying a selection of a selection of a range	swimfast	Excel Worksheet Functions	1	March 1st 07 02:51 AM
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue.	jonH	Excel Programming	3	June 7th 04 09:13 PM
Creating range name for a range selection	Mervyn Thomas	Excel Programming	1	January 26th 04 05:18 PM

Menu

About Us