Range problem

[Jac Tremblay]

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.

 

[Gary''s Student]

Rather than use:
some_range.Value=""
use:
some_range.Clear

 

[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

 

[hanyu.chuang]

when u use range you need to reference with a string such as "A1", and
to select a bunch of cells "A110"
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

 

[Jac Tremblay]

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Jac Tremblay]

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]

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.

 

[Ron Rosenfeld]

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.

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