Best of all worlds, we give you pointers, you solve it :)

Bob


"ryguy7272" wrote in message
...
Your comments pointed me in the right direction Chip! Bob, I used that
Resize trick. Thanks to both of you!

Here's the final version of code:
Dim nmRange As Name
For Each nmRange In ActiveWorkbook.Names
nmRange.Delete
Next

Sheets("TransposedSheet").Select
Dim LstRow As Long
LstRow = wsTrans.Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.Left = 22.75
Application.Top = 100
ActiveWorkbook.Worksheets("TransposedSheet").Range ("A1").Resize(LstRow,
3).Name = "RyanRange"


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Nope, same thing. Error occurs on the same line and this is the error:
wsTrans.Range("RyanRange"). . . . = <Method 'Range' of object
'_Worksheet'
failed

Those named ranges still are not being deleted. There may be some
reference
that is not correct. I'm working in 2007 now. I'm not a huge fan of
this,
but starting to warm up to it.


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"Chip Pearson" wrote:

Do you want to delete the Name (but not the range to which it refers)
or do you want to delete the range of cells along with the name? As
written, you are deleting only the name, not the range to which it
refers.

ActiveWorkbook.Names("RyanRange").Comment = ""

The Comment property was added in XL2007, so it won't work earlier
versions.

Another thing looks suspect. In your code, you add the name
"SummaryTable" but then use the Comment property on the next line
using "RyanRange". Is this supposed to be this way?

Why are you using RefersToR1C1? Just use a normal xlA1 address.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Thu, 4 Feb 2010 12:34:01 -0800, ryguy7272
wrote:

My named ranges will be based on changing numbers of rows. I'm trying
to
delete a named range (just looping to find all) and re-name the range
(for an
eventual import into Access). Below is my code:

Dim nmRange As Name
For Each nmRange In ActiveWorkbook.Names
nmRange.Delete
Next

Sheets("TransposedSheet").Select
Dim LstRow As Long
LstRow = wsTrans.Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.Left = 22.75
Application.Top = 100
ActiveWorkbook.Names.Add Name:="SummaryTable", RefersToR1C1:= _
"=TransposedSheet!R1C1:R" & "LstRow" & "C3"
ActiveWorkbook.Names("RyanRange").Comment = ""

Error message is:
Run-time error '1004'
Application-defined or object-defined error

For one thing, the named ranges are NOT being deleted. For another
thing,
the code fails on the last line, but I can't figure out why, evening
by
F8-ing through the code, the answer isn't apparent to me. So experts,
any
ideas?
.