How to specify a Range which includes an integer variable

[Hotbird]

The following code works, but it is probably 3 times as long as it needs to
be:

Sheets("Printed").Range("O8:O27,I4:I5").ClearContents
Select Case gintRoundN
Case 1: Sheets("Votes").Range("E21:K30").ClearContents
Case 2: Sheets("Votes").Range("F21:K30").ClearContents
Case 3: Sheets("Votes").Range("G21:K30").ClearContents
Case 4: Sheets("Votes").Range("H21:K30").ClearContents
Case 5: Sheets("Votes").Range("I21:K30").ClearContents
Case 6: Sheets("Votes").Range("J21:K30").ClearContents
Case 7: Sheets("Votes").Range("K21:K30").ClearContents
End Select

What I really want is to find a way of inserting a letter as the first
character of the Range ("*21:K30) with E corresponding to 1, F to 2 etc.
Can anyone put me out of my misery?

 

[Rob Bovey]

How about something like this:

Sheets("Votes").Range(Chr$(gintRoundN + 68) & "21:K30").ClearContents

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Frank Kabel]

Hi
try something like

col_index = 1
Case 1:
Sheets("Votes").Range(cells(21,col_index+4),cells(30,11)).ClearContents

 

[Hotbird]

Many thanks for this elegant solution

 

[William DeLeo]

Hello Rob,

Could you please explain the syntax you suggest here: (Chr$(gintRound
+ 68) & "21:K30")

In particular ... what is "Chr$"?

Thank