CODEWITHSUNDEEP
vbaexcel
teepee
teepee

Reputation: 2714

Applying same method to multiple objects

I am curious to know whether there is a way to apply the same method to multiple objects. In other words, I'm looking for something that is, in a way, the inverse of a "With" statement.

I know that using "With" statements, I can apply multiple methods to a single object:

With ITEM_RNG
    .ClearContents
    .ClearFormats
    .ClearOutline
End With

However, I have multiple range objects to which I would like to apply a single method:

ITEM_RNG.ClearContents
VALUE_RNG.ClearContents
SUM_RNG.ClearContents

I have tried to create an array of objects to loop with, but it appears to be more convoluted than the above syntax. Is there an way to do this without explicitly appending .ClearContents to each object individually?

Now, using what I've learned about the Union function, I have tried to employ the following, without success.

Sub ClearBox(SeriesBox As MSForms.ListBox)
    SeriesBox.Clear
End Sub

Sub SeriesBoxesClearall()
    ClearBox Application.Union(SERIES1_BOX, SERIES2_BOX, SERIES3_BOX)
End Sub

Where SERIES[1-3]_BOX are MSForms.ListBox objects. Is there a way to make this work, or does Union not apply here?

Upvotes: 1

Views: 891

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use Application.Union (assuming that all your ranges in the same sheet):

Application.Union(ITEM_RNG,VALUE_RNG,SUM_RNG).ClearContents

or like this:

With Application.Union(ITEM_RNG,VALUE_RNG,SUM_RNG)
    .ClearContents
    .ClearFormats
    .ClearOutline
End With

UPD:

Sub cleanUp(rng As Range)
    rng.ClearContents
End Sub

Sub test()
    Call cleanUp(Application.Union(ITEM_RNG,VALUE_RNG,SUM_RNG))
End Sub

Upvotes: 3

Related Questions