CODEWITHSUNDEEP
excelvbaloops
Incognito
Incognito

Reputation: 351

Define a set of non-adjacent cells

In VBA, several adjacent cells can be modified at once using a "for" loop. For instance:

Dim i As Integer, j As Integer

For i = 1 To 6
    For j = 1 To 2
        Cells(i, j).Value = 100
    Next j
Next i

Now, let's suppose the cells are not adjacent. For instance, that I would like to modify cells A3:B8, D9:V9 and C7:F2. What could I do not to have to write the code three times? Is there a way to define a set of cells and have the code run once for each?

Upvotes: 1

Views: 102

Answers (2)

BigBen
BigBen

Reputation: 49998

You can loop over the Areas:

Sub Test()
    Dim rng As Range
    For Each rng In Range("A3:B8,D9:V9,C7:F2").Areas
        Dim cell As Range
        For Each cell In rng
            cell.Value = 100
        Next
    Next
End Sub

Note that the entire thing can just be replaced by:

Range("A3:B8,D9:V9,C7:F2").Value = 100

but I suppose your actual use case is more complex than this simplified example.

EDIT:

Also simpler:

Dim rng As Range
For Each rng In Range("A3:B8,D9:V9,C7:F2")
    rng.Value = 100
Next

but it's still not clear exactly what you want to do.

Upvotes: 3

FaneDuru
FaneDuru

Reputation: 42236

Please, look at the next code:

  Dim sh As Worksheet, rng As Range, C As Range, i As Long

  Set sh = ActiveSheet
   Set rng = sh.Range("A3:B8, D9:V9, C7:F2")
   rng.Value = 100
   rng.Select 'it is not necessary for the following loop. Only for you to better visualize the range...
   For Each C In rng.cells
      C.Value = C.Value + i: i = i + 1
   Next
   'you can see how the range is parsed (left to right, then down...)
End Sub

Upvotes: 1

Related Questions