Delete Named rnage and Add Named Range

R

ryguy7272

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?
 
B

Bob Phillips

Does this work

Dim nmRange As Name
Dim LstRow As Long

For Each nmRange In ActiveWorkbook.Names
nmRange.Delete
Next

LstRow = wsTrans.Cells(wsTrans.Rows.Count, "A").End(xlUp).Row
Application.Left = 22.75
Application.Top = 100
ActiveWorkbook.Worksheets("TransposedSheet").Range("A1").Resize(LstRow,
3).Name = "SummaryTable"
wsTrans.Range("RyanRange").Comment = ""

HTH

Bob
 
C

Chip Pearson

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 = ""
Click to expand...

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]
 
R

ryguy7272

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 said:
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 = ""
Click to expand...

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]



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?
Click to expand...
.
Click to expand...
 
R

ryguy7272

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 said:
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 said:
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 = ""
Click to expand...

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]



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?
Click to expand...
.
Click to expand...
Click to expand...
 
B

Bob Phillips

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

Bob


ryguy7272 said:
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 said:
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 said:
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

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?
.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

TransferSpreadsheet and NamedRange; not working! 2
Named Range 5
Named Range maker code 9
Resize Range for Column 3
Populating a Named Range & Cell Array 0
Sort Dynamic Range 4
Naming variable range selected by cursor movement 2
Macro to Define Name on a changing selection of cells 5

Top