CODEWITHSUNDEEP
vbaloopsexcel
Toby Booth
Toby Booth

Reputation: 175

Select range using specific criteria within a contiguous column of data

I want to have VBA select a Range, covering a constant number of columns but differing numbers of rows from a contiguous data set in excel, capturing only the rows that contain matching timestamps (in this specific case, although the ability to match any criteria is more dynamic obviously). I want to feed this Range into another macro I have. After my macro has run its process on the captured range, i'd want it to loop through the whole process again using the next captured range in line.

      A       B       C           D       E       F
1     x       x       12:10       x       x       x
2     x       x       12:10       x       x       x
3     x       x       12:10       x       x       x
4     x       x       12:40       x       x       x
5     x       x       12:40       x       x       x
6     x       x       12:40       x       x       x
7     x       x       12:40       x       x       x
8     x       x       1:05        x       x       x
9     x       x       1:05        x       x       x
10    x       x       1:05        x       x       x

For example,

I'd like to capture columns A-F, rows 1-3 as one Range, run my macro, then...

I'd like to capture columns A-F, rows 4-7 as another Range, run my macro, then...

I'd like to capture columns A-F, rows 8-10 as another Range, run my macro, etc... until there aren't any more ranges below this to capture.

As you can see, the rows in this case are differentiated by their timestamps.

I'm just a couple of days in getting to grips with VBA, I have a basic underatanding of what does what, just not the experience to piece this one together from other examples I've found.

Thanks for your help.

Upvotes: 2

Views: 2777

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Probably the simplest way is to:

  1. Copy your data to an array
  2. Inspect the array to find the contiguous ranges (looping this array is much faster than looping the cells)
  3. Call your macro with the identified range
Option Explicit

Sub Demo()
    Dim v As Variant
    Dim i As Long, j As Long
    Dim sh As Worksheet

    Set sh = ActiveSheet
    v = sh.[A1].CurrentRegion ' Assumes data range starts at cell A1
    ' Alternative method to get data block starting at A3
    ' Extend to a specified column number (NumberOfColumnsYouWant)
    ' Assumes all rows in your data block in columns A have data
    v = sh.Range([A3], [A3].End(xlDown)).Resize(, NumberOfColumnsYouWant)


    i = 1
    j = 1
    Do While i <= UBound(v, 1)
        Do While j < UBound(v, 1)
            If v(i, 3) = v(j + 1, 3) Then
                j = j + 1
            Else
                Exit Do
            End If
        Loop
        YourMacro Range(sh.Cells(i, 1), sh.Cells(j, UBound(v, 2)))
        i = j + 1
        j = i
    Loop
 End Sub

 Sub YourMacro(rng As Range)
     MsgBox rng.Address
 End Sub

Upvotes: 4

Related Questions