Wrong Number of Arguments or Invalid Property Assignment???

[tbassngal]

Hi everyone! When I have just two cells listed to .clear, this works.
When I add all the cells I need to be cleared, I get the Compile Error
"Wrong Number of Arguments or Invalid Property Assignment" Should I be
using something else other than Cells???

Sub RemoveStuff()
Dim i As Long
i = 2
While Not IsEmpty(Cells(i, 4))
If Cells(i, 4).Value = Cells(i - 1, 4).Value And Cells(i, 8) <=
0 Then
Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
37), Cells(i, 38)).Clear
ElseIf Cells(i, 4).Value = Cells(i + 1, 4).Value And Cells(i,
8) <= 0 Then _
Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
37), Cells(i, 38)).Clear
End If
i = i + 1
Wend

End Sub

 

[Jim Rech]

Cells(i, 19).resize(20).Clear

is a lot easier.

--
Jim
| Hi everyone! When I have just two cells listed to .clear, this works.
| When I add all the cells I need to be cleared, I get the Compile Error
| "Wrong Number of Arguments or Invalid Property Assignment" Should I be
| using something else other than Cells???
|
| Sub RemoveStuff()
| Dim i As Long
| i = 2
| While Not IsEmpty(Cells(i, 4))
| If Cells(i, 4).Value = Cells(i - 1, 4).Value And Cells(i, 8) <=
| 0 Then
| Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
| 22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
| 27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
| 32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
| 37), Cells(i, 38)).Clear
| ElseIf Cells(i, 4).Value = Cells(i + 1, 4).Value And Cells(i,
| 8) <= 0 Then _
| Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
| 22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
| 27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
| 32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
| 37), Cells(i, 38)).Clear
| End If
| i = i + 1
| Wend
|
| End Sub
|

 

[tbassngal]

So would it be like:

Cells(i,
19).resize(20).resize(21).resize(22).resize(23).resize(24).Clear

???

This doesn't make sense to me... I've never used this before.

 

[Jim Rech]

Just replace your entire .Clear statement with mine. And check out Resize
in Help.

--
Jim
| So would it be like:
|
| Cells(i,
| 19).resize(20).resize(21).resize(22).resize(23).resize(24).Clear
|
| ???
|
| This doesn't make sense to me... I've never used this before.
|