How to build non-consecutive ranges of rows based on cell contents?

Question

I'm just getting started with VBA for Excel. I used VB and Java in college nearly ten years ago and was competent with it then, but am essentially starting over. (Um, not like riding a bike.)

I am trying to understand the methods to build a range that isn't just declared as A1:J34 or whatever. My Googling is challenged in that when searching for 'range' and terms that indicate what I seek, I get an avalanche of hits far more advanced than what I need, mostly hits that don't even address the basic summary info I need.

So, here's the basics of it: Excel 2011 on Mac. The sheet has data from A to M, down to 1309. It's a repeating pattern of heading rows followed by data rows. Ugh. Seems like the person creating the sheet was more thinking about printing from the sheet than the organisation of the data. I need to clean it and 3 more like it up to use in a pivot table, and it's useless in this silly repeating layout.

Heading rows are as follows: Last Name, First Name, then 10 date cells. Data rows under the headings are the names, of course, and then a 1 or 0 for attendance. Anywhere from 20 to 30 names under each heading. Then it repeats. And the dates change every few sets, picking up where the last set left off.

What I need to do right now: I'm trying to assemble a range into a range variable by adding all the rows beginning with a specific value (in column A). In my case that value is the string "Last Name", so I can have the range variable holding all the cells in all rows that begin with "Last Name". This will then capture all the cells that need to be in date format. (I'm doing it so I can then make sure the date headings are all actually IN date format - because they are NOT all in date format now, many are just 'General' cells.)

My questions:

1. When telling a range object what it's range IS, how do you feed it cells/rows/columns that are not just a block defined by start and end cells entered by the person writing the code but based on row criteria? Eg: Create a Range that has rows 1, 34, 70, 93, and 128 from columns A to I based on presence of "First Name" in A.
2. What are the most common methods to do this?
3. Which of these is best suited to my need and why?

Answer 1

You can use the Union operator, like this

Dim r As Range

Set r = Range("A1, A3, A10:A12")

or this

Set r = Union(Range("A1"), Range("A3"), Range("A10:A12"))

You can the iterate this range like this

Dim cl as Range
For Each cl in r.Cells
    ' code cell cl
Next

or this

Dim ar as Range
For each ar in r.Areas
    ' code using contiguous range ar
    For each cl in ar.Cells
        ' code using cell cl
    Next
Next

Answer 2

Here's a working example that demonstrates finding the "Last Name" rows, contructing a range object that includes all those rows, and then iterating through that object to search for non-date values. The code could be speeded up greatly by reading the data range into an array of variants and then searching the array for both the last name rows and the "bad dates" within those rows. This is especially true if you have a very large number of rows to check.

Sub DisjointRng()

    Dim checkCol As String, checkPattern As String
    Dim dateCols()
    Dim lastCell As Range, usedRng As Range, checkRng As Range
    Dim cell As Variant
    Dim usedRow As Range, resultRng As Range, rngArea As Range
    Dim i As Long, j As Long

    checkCol = "A"             'column to check for "Last Name"
    checkPattern = "Last*"
    dateCols = Array(3, 5)     'columns to check for date formatting

    With Worksheets("Sheet1")
        'find the bottom right corner of data range; we determine the used range
            'ourselves since the built-in UsedRange is sometimes out-of-synch
        Set lastCell = .Cells(.Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
            .Cells.Find(What:="*", SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
        Set usedRng = .Range("A1:" & lastCell.Address)
        'the column of values in which to look for "Last Name"
        Set checkRng = .Range(checkCol & "1:" & checkCol & usedRng.Rows.Count)
    End With
    'step down the column of values to check for last name & add
        'add found rows to range object
    For Each cell In checkRng
        If cell.Value Like checkPattern Then
           'create a range object for the row
            Set usedRow = Intersect(cell.EntireRow, usedRng)
            If resultRng Is Nothing Then
                'set the first row with "Last Name"
                Set resultRng = usedRow
            Else
                'add each additional found row to the result range object
                Set resultRng = Union(resultRng, usedRow)
            End If
        End If
    Next cell
    For Each rngArea In resultRng.Areas
        'if found rows are continguous, Excel consolidates them
            'into single area, so need to loop through each of the rows in area
        For i = 1 To rngArea.Rows.Count
            For j = LBound(dateCols) To UBound(dateCols)
                If Not IsDate(rngArea.Cells(i, dateCols(j))) Then
                    'do something
                End If
            Next j
        Next i
    Next rngArea
End Sub