Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

Hi Carlos,
Your point seems logical to me. I will try it right now and post another
answer later on. Thank you for this quick answer.
By the way, why do you use Call? Why not just xlRng.ClearContents?
Another point: I thought that xlRng.Value = "" was the same as
xlRng.ClearContents. Am I right? I do not want to delete the cells or clear
the formats or anything else, I just want the contents to become a null
string.
Thanks again for your answer. I will sleep better tonight.
--
Jac Tremblay


"Carlos" wrote:

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

Hi Carlos,

I did try your code with my application and it works fine. You are a king.

Thanks again.
--
Jac Tremblay


"Carlos" wrote:

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

Hi Jac,

1. Use of "Call". You use call (a) when you do not want to store the result
of a function; and (b) when you use parenthesis while writing the arguments
of a macro.

Example of (a). Suppose you've got the function f(x) = x+2. If you just want
to "call" it you say Call f(x), instead of saying y = f(x). This example is
overly simplistic, but I guess building complex examples is not the case here.

Example of (b). Suppose you've got a macro g(x,y). As you mentioned before,
you can say g x, y and it works, but I prefer to call the function in order
to make it clear what arguments does g uses, so I say Call g(x,y).

2. xlRng.Value = "". This differs from ClearContents in that "" is a value:
it is the null string. This value is even one of Excel's constants, the
vbNullString constant. When you say xlRng.Value = "", you are actually
assigning a value to the cells, while when you say xlRng.ClearContents you
are leaving the range blank (without values). By the way, the .Value property
is the default property of a range object, so you don't have to say
xlRng.Value = z, but just xlRng = z.

I know the distinction between vbNullString and ClearContents might not make
the difference for the vast majority of applications. My intention was to
point out that it is different. For example, try filling some cells with the
formula ="" in a blank worksheet. If you move with the keyboard using Ctrl
and the directional arrows (up, down, left and right), you will find that
Excel treats the cells with "" different from blank cells.

--
Carlos


"Jac Tremblay" wrote:

Hi Carlos,
Your point seems logical to me. I will try it right now and post another
answer later on. Thank you for this quick answer.
By the way, why do you use Call? Why not just xlRng.ClearContents?
Another point: I thought that xlRng.Value = "" was the same as
xlRng.ClearContents. Am I right? I do not want to delete the cells or clear
the formats or anything else, I just want the contents to become a null
string.
Thanks again for your answer. I will sleep better tonight.
--
Jac Tremblay


"Carlos" wrote:

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

I use "Call" only when I'm calling a subroutine that I wrote. I don't usually
use it for my own functions. I'll never use it a builtin VBA function.

And the only difference I've seen between rng.clearcontents and rng.value = ""
is when there is a merged cell in the range. The .clearcontents won't work (as
written). Assigning the value will work.

Jac Tremblay wrote:

Hi Carlos,
Your point seems logical to me. I will try it right now and post another
answer later on. Thank you for this quick answer.
By the way, why do you use Call? Why not just xlRng.ClearContents?
Another point: I thought that xlRng.Value = "" was the same as
xlRng.ClearContents. Am I right? I do not want to delete the cells or clear
the formats or anything else, I just want the contents to become a null
string.
Thanks again for your answer. I will sleep better tonight.
--
Jac Tremblay

"Carlos" wrote:

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay

--

Dave Peterson

Hi Carlos,
I tested your version of my code and it only works when I just inserted some
data. I does not work on actual data (inserted before).
Here is the code with the problem.
' ***
With rngSuppr
..Range(.Cells(intIndex + 1, 1), _
..Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
I patched the problem like thsi 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
' ***
Would you be able to tell me what the problem is.
Thanks
--
Jac Tremblay


"Carlos" wrote:

Hi Jac, the problem is that when you say Cells(i,j) you are not specifying if
Cells(i,j) belongs to a worksheet or to another range object. Let wks be a
workhseet and xlRng be a range. You should do something like this:

'Note the "." before "Cells(i,j)"
With wks
Set xlRng = .Range(.Cells(1,1),.Cells(10,2))
Call xlRng.ClearContents
End with

If you do not add the dot, Excel does not know what cells are you referring
to. Finally, you should use the ClearContents method instead of assigning the
null string "" to the data range.

The following macro implements this code

Option Explicit
Public Sub subDelete()
'This macro deletes the contants of the range A1:B10
'
'Variables:
'wks = Worksheet "sheet01" of the Active Workbook
'xlRng = A range
'
'
Dim wks As Worksheet
Dim xlRng As Range

'Assign wks
Set wks = ActiveWorkbook.Worksheets("sheet01")

'Assign xlRng (note the "." before Cells)
With wks
Set xlRng = .Range(.Cells(1, 1), .Cells(10, 2))
End With

'There are many operations that apparently look the same, but they aren't.
Call xlRng.ClearContents 'Clear contents
'xlRng = "" 'Every value in the range is set equal to the null string ""
'xlRng.Delete 'The whole range is eliminated from the worksheet

'Clean
Set wks = Nothing
Set xlRng = Nothing

End Sub

--
Carlos


"Jac Tremblay" wrote:

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
--
Jac Tremblay