Having problems with simplifying Range Selection code

[MonsterBait]

Hi

I was wondering if someone could show me how to simplify this code?
I use Excel 2007

I would like to clear the contents of these named cells.
They all share a common prefix.
The code below works, but I have 20 name cells to clear and it would be really messy to type the lot out.

Sub ClearContents()
Sheets("Sheet1").Range("TestName, TestCategory, TestNumber").Select
Selection.ClearContents

End Sub

Any help will be appreciated.

Cheers,
Alex

 

[soccerkingpilot]

Instead of putting the names in the .Range(), you could put them in an array and create a loop that runs through the array variable with .Range(arrayvar)

Let me know if you need code for it.

-Austin

 

[MonsterBait]

Hi Austin,

If you could show me the code, that would be great.
I'm still very new at this.

Thanks.
Alex

Instead of putting the names in the .Range(), you could put them in an array and create a loop that runs through the array variable with .Range(arrayvar)

Let me know if you need code for it.

-Austin

 

[Trebor76]

Hi MonsterBait,

Try this:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=640341
    
    Dim nmeMyRange As Name
    
    Application.ScreenUpdating = False
  
    For Each nmeMyRange In ActiveWorkbook.Names
        If Left(nmeMyRange.Name, 4) = "Test" Then
            Sheets(CStr(Range(nmeMyRange).Worksheet.Name)).Range(nmeMyRange).ClearContents
        End If
    Next nmeMyRange
    
    Application.ScreenUpdating = True
        
End Sub

Regards,

Robert

 

[tusharm]

Well, you have to type the list somewhere. Otherwise, you'd need a mindreading computer to do the work. {grin}

As an alternative, how about adding an additional name the encompasses all the cells you want to clear? Then, just clear this one range.

Hi

I was wondering if someone could show me how to simplify this code?
I use Excel 2007

I would like to clear the contents of these named cells.
They all share a common prefix.
The code below works, but I have 20 name cells to clear and it would be really messy to type the lot out.

Sub ClearContents()
Sheets("Sheet1").Range("TestName, TestCategory, TestNumber").Select
Selection.ClearContents

End Sub

Any help will be appreciated.

Cheers,
Alex

 

[MonsterBait]

Hi Trebor76,

The code is getting stuck on -
Sheets(CStr(Range(nmeMyRange).Worksheet.Name)).Range(nmeMyRange).ClearContents

but I can't work out why. Am I missing something?



Hi Tusharm,

Where can I buy a mind reading computer??
I was hoping to create a loop that will go looking for Named Cells with the same prefix, select those and clear the lot.

Well, you have to type the list somewhere. Otherwise, you'd need a mindreading computer to do the work. {grin}

As an alternative, how about adding an additional name the encompasses all the cells you want to clear? Then, just clear this one range.

 

[Trebor76]

The code is getting stuck on -
Sheets(CStr(Range(nmeMyRange).Worksheet.Name)).Range(nmeMyRange).ClearContents

That's odd as it worked for me

Make sure that none of the ranges are corrupt, i.e. when you display the Name Manager dialog (Ctrl + F3) there are no #REF! in the ranges the code is trying to work with.

 

[MonsterBait]

Hi all,

Thanks for helping me out. I've been meaning to come back on to thank you all for your input in helping but between this site being down and me being on holidays, its a couple of weeks late.
Incidentially, I found a way in doing what I needed.

Cheers,
Alex