Accessing ranges with integer variables

[Andrew]

Hello,
I need a snippet of code to implement the code show here, but
using variables as the range argument.

Worksheets("graphs").Range("A1:B200").ClearContents

What I would like to do is replace "A1:B200" with an integer value (to
be entered by user) of 200. Is there a way I can do this using
Cells(x,y)? Can the Cells command be used to access a range, as
opposed to a single cell?

thanks
Andy

 

[sebastienm]

Hi,
try something like:
''' --------------------------------------------------
Sub test()
Dim i As Long, maxrow As Long
Dim strResult As String

maxrow = ActiveSheet.Rows.Count

''' ask the user
strResult = VBA.InputBox("Enter row (number greater than 0):", "Clear
data")

If strResult = "" Then '''user cancelled
Exit Sub
ElseIf Not IsNumeric(strResult) Then ''' user didn't enter a number
Else
i = Val(strResult) ''' convert to number
If i < 1 Or i > maxrow Then
MsgBox "The number must between 1 and " & maxrow
Exit Sub
Else
Worksheets("graphs").Range("A1:B" & i).ClearContents
End If
End If

End Sub
''' ------------------------------------------

 

[Jerry W. Lewis]

Worksheets("graphs").Range(Cells(1,1),Cells(200,1)).ClearContents

Jerry

 

[Alan Beban]

Hello,
I need a snippet of code to implement the code show here, but
using variables as the range argument.

Worksheets("graphs").Range("A1:B200").ClearContents

What I would like to do is replace "A1:B200" with an integer value (to
be entered by user) of 200. Is there a way I can do this using
Cells(x,y)? Can the Cells command be used to access a range, as
opposed to a single cell?

thanks
Andy

x = 10
Debug.Print Range("A6:B" & x).Address

 

[Dave Peterson]

Just a clarification on Jerry's post.

If Graphs isn't the activesheet, then those unqualified ranges (cells()) will
cause trouble with the code.

I'd use:

with worksheets("Graphs")
.range(.cells(1,1), .cells(200,1)).clearcontents
end with

Those dots in front of the .range & .cells mean that they belong to the object
referred to in the previous "with" statement--in this case the Graphs worksheet.