vba - 循环遍历工作表中的区域/区域？

Question

我有一个工作表，其中包含我想循环遍历的许多不同区域。我将进行一些计算，并认为它比在列中逐个单元格地更快/更有效。

我尝试了一些不同的东西，但不知道如何跳到下一个区域。我已经发表了一些评论，最有希望的是最后一个( For each cel in rng... ，但是在我做了第一个 cel.CurrentRegion.Select 之后，然后再做一些事情，我该如何跳转到下一个区域？

Here's a zany .gif of what that does as-is...

Sub loop_through_zones()
Dim rng As Range, area As Range, singleArea As Range, cel As Range
Set rng = Range("A2:D15")

For Each area In rng.Areas ' This just selects all the data.
    area.Select
Next area

For Each area In rng.CurrentRegion ' this just loops through cells in an area.
    area.Select
Next area

For Each cel In rng
    cel.CurrentRegion.Select ' gets current region!
    'do something with region here
   ' ...
    ' now, go to the next REGION, not cel in current area...?
Next cel

End Sub

所以我想得到 A2:D4 ，做事，然后继续下一个区域， A6:D9 ，然后转到 A11:D15 ， ETC。

编辑:看起来我可以用一些 For i 来做到这一点循环，但我很好奇你是否可以使用内置的 CurrentRegion/ Areas ，或者如果我必须这样做很笨拙:

For i = 2 To lastRow
    Set CurrentRegion = .Range(.Cells(i, 1), .Cells(.Cells(i, 9).End(xlDown).row, 4))
    CurrentRegion.Select 
    ' Do things with the current region here...
    i = CurrentRegion.Rows(CurrentRegion.Rows.Count).row + 1
Next i

Answer 1

你可以试试这样的...

Sub LoopThroughZones()
Dim lr As Long, iRow As Object
Dim Area As Range, Rng As Range, Cell As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row

'Loopting through each block
For Each Area In Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
    Area.Resize(, 4).Select
Next Area

'Looping through each cell in each block
For Each Area In Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
    Set Rng = Area.Resize(, 4)
    For Each Cell In Rng
        Cell.Select
    Next Cell
Next Area
End Sub