Solved: Deleting data within a named range (xl '97)

[phendrena]

Hi there,

I have the following code :
[VBA]Sub ClearRanges()
Dim ws As Worksheet
Dim nm As Name
Set ws = Worksheets("TestCode")
For Each nm In ActiveWorkbook.Names
ws.Range(nm).Select
Selection.ClearContents
Next
End Sub[/VBA]

Currently the code selects the named range and then clears the contents. However, i will be using the above code in a workbook that has hidden sheets.

Would anyone please be able to offer a solution that doesn't involve actually selecting the named ranges before clearing the conents of the named range?

Thanks,

[Bob Phillips]

[vba]

Sub ClearRanges()
Dim ws As Worksheet
Dim nm As Name
Set ws = Worksheets("TestCode")
For Each nm In ActiveWorkbook.Names
ws.Range(nm).ClearContents
Next
End Sub
[/vba]

[phendrena]

Thanks xld.
A simple change to the code does the trick.

I bet you look forward to my posts with utter horror!!

Cheers,

[phendrena]

Hmmmm......
ActiveWorkbook
One assumes that using this it will delete ALL the data in ALL the ranges in the entire workbook. Can this be changed to work on specfic worksheets sheets only?

Thanks,

Scratch that, the code already references a specific worksheet. The code does error if it detects a named range outside of the named sheet though.
As i'd expect this i'll add a 'on error resume next' line.
Unless you could suggest a way to avoid using this?

Thanks,

[Bob Phillips]

You could add on error, but to be honest, I try to avoid this where I can, so I would test it

[vba]

Sub ClearRanges()
Dim ws As Worksheet
Dim nm As Name

Set ws = Worksheets("TestCode")
For Each nm In ActiveWorkbook.Names

If ws.Evaluate(nm.RefersTo).Parent.Name = ws.Name Then _
ws.Range(nm).ClearContents
Next
End Sub
[/vba]

I bet you look forward to my posts with utter horror!!

Absolutely not, if I did I wouldn't read them.

I see you as someone who is not a programmer, probably doesn't want to be, but who understands how they can get the best of of themselves and the job by pushing Excel and VBA. I like that you always try things, you are clear in your explanations, and you give the code. Most of all, you are smart enough to call for help when you need it, and even smarter to use VBAX

[phendrena]

You could add on error, but to be honest, I try to avoid this where I can, so I would test it

I would have to agree, best practice is to avoid using 'resume next'.

[vba]If ws.Evaluate(nm.RefersTo).Parent.Name = ws.Name Then _
ws.Range(nm).ClearContents[/vba]

Evaluate - i didn't even know you could do this. An interesting one that I can see myself using in the future. I had a quick look in the help file, and it suggests that you can use square brackets [] , for example [A1] instead of using Evaluate("A1").

Other than it being shorter are there any advantages to using this one? Personally i'd stick with the longer version as it'd make the code clearer but i suppose it personaly preference.

Absolutely not, if I did I wouldn't read them.

I see you as someone who is not a programmer, probably doesn't want to be, but who understands how they can get the best of of themselves and the job by pushing Excel and VBA. I like that you always try things, you are clear in your explanations, and you give the code. Most of all, you are smart enough to call for help when you need it, and even smarter to use VBAX

I am indeed not a programmer, but I would like to be and i'm having immense fun learning VBA - something I never thought i be able to understand. But through various books, trial and error and the assistance of people on this forum i've learnt so much (but also, in greater scheme, so little).

I'd eventually like to make a jump into VB as it seems a logical(-ish) step.
I'm not naive enough to think VBA & VB are anything alike but i'm hopeful that some of the things i've learnt through my ongoing VBA experiences will help me learn another language a little easier (and probably confuse the heck out of me!).

I thank you for your kind words and ongoing help.
It is highly appreciated. I shall continue to try stuff out and also post examples with clear thoughts and questions.

I owe you many beers!

[Bob Phillips]

Evaluate - i didn't even know you could do this. An interesting one that I can see myself using in the future. I had a quick look in the help file, and it suggests that you can use square brackets [] , for example [A1] instead of using Evaluate("A1").

Don't do it! I think that is incredibly sloppy practice, and if you use it you will have me on your back every time berating you

Other than it being shorter are there any advantages to using this one? Personally i'd stick with the longer version as it'd make the code clearer but i suppose it personaly preference.

Exactly. Defensive programming as I call it, maintaining code is harding than writing it.

I am indeed not a programmer, but I would like to be and i'm having immense fun learning VBA - something I never thought i be able to understand. But through various books, trial and error and the assistance of people on this forum i've learnt so much (but also, in greater scheme, so little).

Great. You want to make your living from it?

I'd eventually like to make a jump into VB as it seems a logical(-ish) step.
I'm not naive enough to think VBA & VB are anything alike but i'm hopeful that some of the things i've learnt through my ongoing VBA experiences will help me learn another language a little easier (and probably confuse the heck out of me!).

VB is VERY MUCH like VBA. There are some richer tools in VB and you don't have direct access to the object model, but via automation you can do it all with VB.

VB.Net - now that is a different matter, a whole new paradigm.

I owe you many beers!

Well, if you ever came to our Excel conference, you could buy me one

[phendrena]

Great. You want to make your living from it?

Eventually, but I can see that being a long way down the line, for now i am happy to keep it as a very interesting and fun pastime.

VB is VERY MUCH like VBA. There are some richer tools in VB and you don't have direct access to the object model, but via automation you can do it all with VB.

I'm very glad to hear that. As it'll make the transition between VBA & VB that little bit easier. I'd really love to setup my excel/access project as a standalone application that isn't limited by the two applications. Of course, it'll be along time before that becomes a reality!

Well, if you ever came to our Excel conference, you could buy me one

I'll get to one eventually. I was gutted I had to work for the one earlier in the year and current finances prevented me from looking at the next one. Hopefully next year eh?