Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
....but it doesn's work. I just want to erase the contents of the cells in
the specified range.
I patched the problem like this but I do not like that solution.
' ***
Dim intK As Integer
With rngSuppr
For intK = 1 To intNbCol
.Cells(intIndex + 1, intK).Value = ""
Next intK
End With
' ***
Can someone tell me what the right syntax is?

Thanks.
--
Jac Tremblay

Rather than use:
some_range.Value=""
use:
some_range.Clear
--
Gary''s Student - gsnu200812


"Jac Tremblay" wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.
I patched the problem like this but I do not like that solution.
' ***
Dim intK As Integer
With rngSuppr
For intK = 1 To intNbCol
.Cells(intIndex + 1, intK).Value = ""
Next intK
End With
' ***
Can someone tell me what the right syntax is?

Thanks.
--
Jac Tremblay

Hi Gary,
That is not the point. I could use .ClearContents or any other ClearStuff
method, the code doesn't work because the
rngSuppr.Range(rngSuppr.Cells(intIndex + 1, 1), rngSuppr.Cells(intIndex + 1,
intNbCol))... doesn't do the job.
The problem is in there...
Thanks
--
Jac Tremblay


"Gary''s Student" wrote:

Rather than use:
some_range.Value=""
use:
some_range.Clear
--
Gary''s Student - gsnu200812


"Jac Tremblay" wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.
I patched the problem like this but I do not like that solution.
' ***
Dim intK As Integer
With rngSuppr
For intK = 1 To intNbCol
.Cells(intIndex + 1, intK).Value = ""
Next intK
End With
' ***
Can someone tell me what the right syntax is?

Thanks.
--
Jac Tremblay

when u use range you need to reference with a string such as "A1", and
to select a bunch of cells "A1:D10"
also any single range like cels(1,1), use cells(1,1).address to return
the string value of address

so

With rngSuppr
.Range(.Cells(intIndex + 1, 1).address & ":" & .Cells(intIndex + 1,
intNbCol).address).Value = ""
End With

Hi Hanyu,
I tried your suggestion and it still doesn't work. I tried different version:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1).Address, _
.Cells(intIndex + 1, intNbCol).Address).Value = ""
End With ' Doesn't work.
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1).Address & ":" & _
.Cells(intIndex + 1, intNbCol).Address).Value = ""
End With ' Doesn't work.
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1).Address(External:=True) & ":" & _
.Cells(intIndex + 1, intNbCol).Address(External:=True)).Value = ""
End With ' Doesn't work.
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1).Address(External:=True), _
.Cells(intIndex + 1, intNbCol).Address(External:=True)).Value = ""
End With ' Bugs (gives an error).
' ***
So I will have to keep my macramé way of doing until I can find the problem.
Thanks for your time. I appreciate.
--
Jac Tremblay


" wrote:

when u use range you need to reference with a string such as "A1", and
to select a bunch of cells "A1:D10"
also any single range like cels(1,1), use cells(1,1).address to return
the string value of address

so

With rngSuppr
.Range(.Cells(intIndex + 1, 1).address & ":" & .Cells(intIndex + 1,
intNbCol).address).Value = ""
End With

On Mon, 10 Nov 2008 18:08:01 -0800, Jac Tremblay
wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.

Possibly you want something like:

Range(rngSuppr(intIndex + 1, 1), _
rngSuppr(intIndex + 1, intNbCol)).ClearContents

--ron

On Tue, 11 Nov 2008 14:06:50 -0500, Ron Rosenfeld
wrote:

On Mon, 10 Nov 2008 18:08:01 -0800, Jac Tremblay
wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.

Possibly you want something like:

Range(rngSuppr(intIndex + 1, 1), _
rngSuppr(intIndex + 1, intNbCol)).ClearContents

--ron


Another format, that more closely matches what you tried:

With rngSuppr
Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
--ron

Hi Ron,
You are absolutely correct. Both ways work fine. It seems that the period
before the Range object was the problem. I tried these two versions that give
the same result.
Range(rngSuppr(intIndex + 1, 1), rngSuppr(intIndex + 1, _
intNbCol)).ClearContents
Range(rngSuppr(intIndex + 1, 1), rngSuppr(intIndex + 1, _
intNbCol)).Value = ""
I tried them also when the worksheet where the data was beeing erased was
not the active one and they both work fine.
Thank you very much and have a good day.

--
Jac Tremblay


"Ron Rosenfeld" wrote:

On Mon, 10 Nov 2008 18:08:01 -0800, Jac Tremblay
wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.

Possibly you want something like:

Range(rngSuppr(intIndex + 1, 1), _
rngSuppr(intIndex + 1, intNbCol)).ClearContents

--ron

Hi again Ron,
I tried this version as well and it is perfect. My problem now is to choose
between the three version that work well.
With rngSuppr
Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With

Thanks again.
--
Jac Tremblay


"Ron Rosenfeld" wrote:

On Tue, 11 Nov 2008 14:06:50 -0500, Ron Rosenfeld
wrote:

On Mon, 10 Nov 2008 18:08:01 -0800, Jac Tremblay
wrote:

Hi, I have this code that should work:
' ***
With rngSuppr
.Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
...but it doesn's work. I just want to erase the contents of the cells in
the specified range.

Possibly you want something like:

Range(rngSuppr(intIndex + 1, 1), _
rngSuppr(intIndex + 1, intNbCol)).ClearContents

--ron


Another format, that more closely matches what you tried:

With rngSuppr
Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With
--ron

On Tue, 11 Nov 2008 12:09:03 -0800, Jac Tremblay
wrote:

Hi again Ron,
I tried this version as well and it is perfect. My problem now is to choose
between the three version that work well.
With rngSuppr
Range(.Cells(intIndex + 1, 1), _
.Cells(intIndex + 1, intNbCol)).Value = ""
End With

Thanks again.
--
Jac Tremblay

Without actually measuring the time to accomplish the task using the different
codings, my bias would be to use the shortest (fewest key strokes) or the one
with the fewest "calls".

But I'm glad you've got it working now.
--ron