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

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
''' ------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Andrew" wrote:

> 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
>

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

Jerry

"Andrew" wrote:

> 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
>

Andrew wrote:
> 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

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.

Jerry W. Lewis wrote:
>
> Worksheets("graphs").Range(Cells(1,1),Cells(200,1)).ClearContents
>
> Jerry
>
> "Andrew" wrote:
>
> > 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
> >

--

Dave Peterson