0 votes
in VBA by Beginner (5 points)

Hi 

I've been trying to write some code that will do a for/next loop through a non-contiguous range (referenced by named ranges, in case it makes a difference), but have been struggling to get anything working. 

When referencing the range, instead of using the next item, it refers to the next cell. 

Eg


For i = 1 To rngCategoryTops.Count
Debug.Print rngCategoryTops(i)
Debug.Print rngCategoryTops(i + 1)
Next

What I'm actually trying to achieve (and perhaps there is a better way, is to have some VBA script parse a report worksheet and format it, depending on how many rows are required, either adding or removing rows as required. I've written code that goes through each section individually, but rather than have this duplicated according to how many sections there are, I'm much prefer to loop this. 

I want to use For/Next rather than For Each, as I also need to refer to what is next up in the range, find it's location and work out how much space is needed so that the array doesn't #spill or so that there is only 2 blank rows in betwen each section. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any help as to best do this would be gratefully received. 

My thanks in advance. 

by Beginner (56 points)

try 

for each cel in rngcategoryTops.cells

 

1 Answer

0 votes
by Expert (801 points)
selected ago by
 
Best answer

Consider the Range.Areas property/collection, the Range.Cells property, and the Range.Item property. For a non-contiguous range, each member of the Areas collection is a contiguous sub-range. Your question doesn't make it clear how you intend to reference the worksheet's cells, but maybe this will help:

    Dim rArea As Range
    For Each rArea In rngCategoryTops.areas
        Debug.Print rArea.Address, rArea.Cells.Count
        For i = 1 To (rArea.Cells.Count - 1) Step 2
            Debug.Print rArea.Cells(i), rArea.Cells(i + 1)
        Next i
    Next rArea

 

by Expert (801 points)

You might also be interested in the Range.CurrentRegion property.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...