Help with Emptying a Collection

[pacer1]

Hello,

I am trying to empty a collection and am receiving an error message:

Run-time error '424':

Object Required



Sub CollectionEmptyTry()
Dim Obj As String
Obj = Range("P17").Value

If Cells(1, 1) <> "" Then
CheckEmpty.Add Obj
End If

If CheckEmpty.Count > 20Then
Set CheckEmpty = Empty
End If

MsgBox CheckEmpty.Count


End Sub


Any help with this error would be greatly appreciated.

Also any suggestions on different ways to empty a collection?

Is there any way to remove more than 1 item with the Remove Method?
CheckEmpty.Remove (1 to CheckEmpty.count) ???

Thanks,

pace

 

[papou]

Hello
Erase CheckEmpty

HTH
Cordially
Pascal

 

[Dave Peterson]

How about just setting the collection to a new collection:

If CheckEmpty.Count > 20 Then
Set CheckEmpty = New Collection
End If

or if you really, really want to remove the items from the collection:

Option Explicit
Sub CollectionEmptyTry()
Dim CheckEmpty As Collection
Dim iCtr As Long

Set CheckEmpty = New Collection

Dim Obj As String
Obj = Range("P17").Value

For iCtr = 1 To 25
If Cells(1, 1) <> "" Then
CheckEmpty.Add Obj
End If
Next iCtr
If CheckEmpty.Count > 20 Then
For iCtr = CheckEmpty.Count To 1 Step -1
CheckEmpty.Remove iCtr
Next iCtr
End If

MsgBox CheckEmpty.Count


End Sub

 

[pacer1]

Thanks Pascal for the suggestion

I am still receiving an error when trying Erase:

Compile Error:
Expected Array

Thanks

Pace

 

[papou]

Hello Pace
Sorry I should have noticed your mention of a collection
My method works with an array
So if you need to have a collection then you should consider Dave Peterson's
answer.

HTH
Cordially
Pascal

 

[pacer1]

Dave your:

If CheckEmpty.Count > 20 Then
Set CheckEmpty = New Collection
End If

does exactally what I am looking for. Thanks.

One other quick question. You used:

Option Explicit
Sub CollectionEmptyTry()
Dim CheckEmpty As Collection


Set CheckEmpty = New Collection

Is there any preference over just using:

Option Explicit
Dim CheckEmpty as New Collection

Thanks,

Pace

 

[Tom Ogilvy]

A discussion saying not to DIM as New is found at Chip Pearson's site:

http://www.cpearson.com/excel/variables.htm

it is a little short of 2/3rds down the page:

section title is:
"Don't Use The New Keyword In A Dim Statement"

 

[pacer1]

Thanks Tom

 

[Dave Peterson]

Chip Pearson explains it at:
http://www.cpearson.com/excel/variables.htm

Look for:
Don't Use The New Keyword In A Dim Statement

 

[Dave Peterson]

I didn't see your reply (well, until after I scrolled down).

 

[Tom Ogilvy]

Look at the bright side. At least we gave the same answer<g>