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

Hello
Erase CheckEmpty

HTH
Cordially
Pascal

"pacer1" a écrit dans le message de news:
...
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

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 wrote:

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

--

Dave Peterson

Thanks Pascal for the suggestion

I am still receiving an error when trying Erase:

Compile Error:
Expected Array

Thanks

Pace

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" a écrit dans le message de news:
...
Thanks Pascal for the suggestion

I am still receiving an error when trying Erase:

Compile Error:
Expected Array

Thanks

Pace

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

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"

--
Regards,
Tom Ogilvy


"pacer1" wrote:

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

Thanks Tom

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

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

pacer1 wrote:

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

--

Dave Peterson

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

Tom Ogilvy wrote:

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"

--
Regards,
Tom Ogilvy

"pacer1" wrote:

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



--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

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

Tom Ogilvy wrote:

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"

--
Regards,
Tom Ogilvy

"pacer1" wrote:

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



--

Dave Peterson