Reset / Clear the Union Object

DominicB

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi all

I have a piece of code which is to loop through all the worksheets in a workbook and select only those cells containing a formula, for a selective find / replace to take place. I am using the Union object thus :

Code: 
For Each UsrRng In Selection
If UsrRng.HasFormula = True Then
Found = Found + 1
If Found = 1 Then Set CellCollection = UsrRng
If Found <> 1 Then Set CellCollection = Union(CellCollection, UsrRng)
End If
Next UsrRng
CellCollection.Select

This works perfectly on the first worksheet, but falls down on the next sheet in line – the debugger pointing to this command as the problem:

= Union(CellCollection, UsrRng)

Is this because the Union still contains range information from the first worksheet? How can I clear it out?

Thanks

DominicB
 
Sort by date Sort by votes
Dominic

I think that might be the problem, you can't union across worksheets

Why not just use this before you move onto the next worksheet.
Code: 
Set  CellCollection = Nothing
 
Upvote 0
Hi Norie

Thanks for the suggestion - I actually thought that would sort it out :)

But is hasn't :-(

Any other thoughts or suggestions?

DominicB
 
Upvote 0
Dominic

I honestly thought that would have done it too.

Didn't test it as it seemed so obvious.:oops:

Can you post the rest of your code?
 
Upvote 0
Right, just tested.

Need to set Found to 0 as well.:)
 
Upvote 0
Hi Norie

Sure.

Code: 
For Each WkBk In ActiveWorkbook.Worksheets
Set CellCollection = Nothing
WkBk.Select
Range("A1").Select
Set LastCell = Cells(Range(Cells(1, 1), ActiveSheet.UsedRange).Rows.Count, Range(Cells(1, 1), ActiveSheet.UsedRange).Columns.Count)
Set ag = Selection.Resize(LastCell.Row, LastCell.Column)
For Each UsrRng In ag
If UsrRng.HasFormula = True Then
Found = Found + 1
If Found = 1 Then Set CellCollection = UsrRng
If Found <> 1 Then Set CellCollection = Union(CellCollection, UsrRng)
End If
Next UsrRng
CellCollection.Select
Selection.Replace What:=Rnme1, Replacement:=Rnme3
Next WkBk

Basically, ag is setting the whole used range of a worksheet, from A1, to the last used row / column. UsrRng then goes through each cell and if it's a formula, it's added to the Union object, which now is cleared prior to each sheet being "done".

It works beautifully. On the first sheet :-?

Thanks for taking the time to look.

DominicB
 
Upvote 0
Aaaaaaaaaargh!!!!!

How could I miss that one?!?!? :oops:

Sometimes I'm so stupid. And you're not!

Cheers Norie

DominicB
 
Upvote 0
What about using SpecialCells(xlCellTypeFormulas)? Something like
Code: 
Dim wks As Worksheet

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
    wks.Cells.SpecialCells(xlCellTypeFormulas).Replace What:=Rnme1, Replacement:=Rnme3
Next wks

Set wks = Nothing
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA Consolidating Multiple Sheets in a Pivot Table
Replies
1
Views
355
c_clark
C
M
  • Solved
VBA to clear cell contents and conditional formatting but retain the formula
Replies
5
Views
884
MLC1980
M
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
544
Kscoof
K
I
  • Solved
Search worksheet for with a changeable column search
Replies
4
Views
109
mumps
mumps
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
519
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top