Working with multiple Ranges without a Loop (Union)
Just wanted to quickly put something together to highlight a very powerful function built into the Excel Application Object that is very underused, Union.
Quite simply it interacts with a variable number of Excel Ranges and will interact with them all together not with a Loop.
Take for example a Loop that is looking at Columns and Deletes them as such:
Dim lngColumn As Long
For lngColumn = 7 To 120
Columns(lngColumn).Delete
Next lngColumn
I see this kind of thing a lot, occasionally the user will notice that some columns are being ignored and this is because once you have processed the deletion of Column 7 Column 8 has moved into Column 7 and will therefore be skipped as lngColumn increments up to 8.
Many of us, having learnt the hard way, will then work out we need to loop backwards and end up with something like this:
Dim lngColumn As Long
For lngColumn = 120 To 7 Step -1
Columns(lngColumn).Delete
Next lngColumn
Still, it's going to take a few seconds to loop through and delete those columns for us and if there are heavy dynamic formula reading the table the slow down could be heavy.
There is however the unheralded Union Function that processes all the changes together and grid shift will never be an issue. Here are a few examples of the things we can do with our Unioned collection of Ranges:
Application.Union(Columns(2), Columns(4), Columns(8)).Delete
Application.Union(Columns("B:D"), Columns("P:V"), Columns("AB:AE")).Delete
Application.Union(Range("rng_1"), Range("rng_2"), Range("rng_3")).ClearContents
Application.Union(Range("B1"), Range("E7"), Range("P12")).Value = Date
Hope some of you find this useful.
President at true north usa, llc
7 年See below some other VBA loop and enumeration strategies when working with INTERSECT, UNION and example code for "DIFFERENCE" when assembling (and Cropping) Multi-Area Ranges. Run it using VBE Application window and hit RUN after each incremental STOP in code. Option Explicit Sub Demo_01() Dim sR As Range, SC As Range, sRC As Range, i As Long, Imax As Integer Dim SCC As Range, NSC As Range, c As Range, n As Long With ActiveSheet: Set sR = .Cells(1, 1): Set SC = sR: Imax = 51 Set sRC = Range(.Cells(1, 1), .Cells(Imax, Imax)) ActiveWindow.Zoom = 25 For i = 1 To Imax Step 5 Set sR = Union(sR, .Rows(i)): Set SC = Union(SC, .Columns(i)) Next i SC.EntireColumn.ColumnWidth = 3# SC.Select: Stop: sR.Select: Stop: Union(sR, SC).Select: Stop Intersect(SC, sRC).Select: Stop: Intersect(sR, sRC).Select: Stop Set SCC = Intersect(Union(sR, SC), sRC) SCC.Select: Stop For Each c In sRC.Cells If (Intersect(SCC, c) Is Nothing) Then n = n + 1 If n > 1 Then Set NSC = Union(NSC, c) Else Set NSC = c End If Next c NSC.Select: Stop: End With: ActiveWindow.Zoom = 50 End Sub
Technical Fellow at NAFEMS
7 年Other strategies include some that are slightly less sensitive to position. For example if the columns for deletion are those of a table, the construct For Each oLC In oLO.ListColumns If ... Then oLC.Delete Next will run through the columns in a systematic manner. Any attempt to use "oLC.Index" as a criterion would still require care though. More generally, if the ranges can all be named at sheet level before being referenced by VBA, the interaction is likely to go more smoothly. Union and intersection operators also exist as worksheet formulas. I use them but most users do not.
--
7 年Yes, the Union() method is useful and this is a good reminder. Also very useful, IMO, is the very similar Intersect() method, which finds frequent use in event handlers, e.g., Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Application.Union(Range("B1:B3"), Range("D1:D3"), Range("F1:F3"))) Is Nothing Then Target.Interior.ColorIndex = xlNone Else Target.Interior.Color = vbRed End If End Sub The above determines if the change occurs in the specific ranges B1:B3, D1:D3, F1:F3. You may want to include a check to see if the target range is one cell or multiple cells; if multiple cells, you may want to skip.