named ranges in a For Loop

I have named ranges in a worksheet (ques1, ques2,etc.). I need a loop
to clear the contents of these ranges, something like:

For i = 1 To 60
Range("ques & i").Select
Selection.ClearContents
Next i

Any suggestions on how to make this work?

i think jim gave you a suggestion in your original post 2 days ago. did you
try it?

--


Gary


<[email protected]> wrote in message
news:[email protected]...
>I have named ranges in a worksheet (ques1, ques2,etc.). I need a loop
> to clear the contents of these ranges, something like:
>
> For i = 1 To 60
> Range("ques & i").Select
> Selection.ClearContents
> Next i
>
> Any suggestions on how to make this work?
>

Try

For i = 1 To 60
Range("ques" & i ).ClearContents
Next i


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<[email protected]> wrote in message
news:[email protected]...
>I have named ranges in a worksheet (ques1, ques2,etc.). I need a
>loop
> to clear the contents of these ranges, something like:
>
> For i = 1 To 60
> Range("ques & i").Select
> Selection.ClearContents
> Next i
>
> Any suggestions on how to make this work?
>

TRY,

Dim n As Name
For Each n In ThisWorkbook.Names
If Left(n.Name, 4) = "ques" Then
Range(n).ClearContents
End If
Next

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"[email protected]" wrote:

> I have named ranges in a worksheet (ques1, ques2,etc.). I need a loop
> to clear the contents of these ranges, something like:
>
> For i = 1 To 60
> Range("ques & i").Select
> Selection.ClearContents
> Next i
>
> Any suggestions on how to make this work?
>
>

I'M SORRY! I had forgotton I had already posted this. Jim's solution
gives a Method 'Range' of object '_Global' failed. Chip's solution is
almost the same as Jim's. Gary's earlier solution works wonderfully:

Sub Clear_rnge()
For Each nm In ThisWorkbook.Names
Range(nm).ClearContents
Next
End Sub

Please accept my appologies for double posting and thanks for the help.

> Sub Clear_rnge()
> For Each nm In ThisWorkbook.Names
> Range(nm).ClearContents
> Next
> End Sub

This will clear the contents of ALL names, not just those
beginning with "ques", as the original poster wanted.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<[email protected]> wrote in message
news:[email protected]...
> I'M SORRY! I had forgotton I had already posted this. Jim's
> solution
> gives a Method 'Range' of object '_Global' failed. Chip's
> solution is
> almost the same as Jim's. Gary's earlier solution works
> wonderfully:
>
> Sub Clear_rnge()
> For Each nm In ThisWorkbook.Names
> Range(nm).ClearContents
> Next
> End Sub
>
> Please accept my appologies for double posting and thanks for
> the help.
>

yep, clearly stated in my original post.

--


Gary


"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
>> Sub Clear_rnge()
>> For Each nm In ThisWorkbook.Names
>> Range(nm).ClearContents
>> Next
>> End Sub
>
> This will clear the contents of ALL names, not just those beginning with
> "ques", as the original poster wanted.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> I'M SORRY! I had forgotton I had already posted this. Jim's solution
>> gives a Method 'Range' of object '_Global' failed. Chip's solution is
>> almost the same as Jim's. Gary's earlier solution works wonderfully:
>>
>> Sub Clear_rnge()
>> For Each nm In ThisWorkbook.Names
>> Range(nm).ClearContents
>> Next
>> End Sub
>>
>> Please accept my appologies for double posting and thanks for the help.
>>
>
>