CODEWITHSUNDEEP
vbaexcel
Emman D.
Emman D.

Reputation: 117

Looping through non-contiguous rows selected with a criteria

This is my first post in SO. As you know I am not a professional programmer, just a regular office worker who does some VBA from time to time to get his job done faster.

I would like to loop over a range of cells using VBA that contains a specific flag value in one of its columns. These ranges are non-contiguous since we select the ranges with the successive flag value, and this value changes randomly in each row.

For example in the table below, how could we loop three times (since there are three non-contiguous ranges with successive -1 flags) selecting the first three rows in the first iteration, the 5th and 6th rows in the second run, and selecting the 8th row in the third iteration? 

+-------+---------+------+
| Index |  Value  | Flag |
+-------+---------+------+
|     1 | 0.43534 |   -1 |
|     2 | 0.24366 |   -1 |
|     3 | 0.23654 |   -1 |
|     4 | 0.56854 |    0 |
|     5 | 0.97867 |   -1 |
|     6 | 0.35678 |   -1 |
|     7 | 0.47845 |    0 |
|     8 | 0.74567 |   -1 |
+-------+---------+------+

Upvotes: 1

Views: 1274

Answers (3)

Mrig
Mrig

Reputation: 11727

Following code will give result in message box specifying all the ranges with Flag = -1. As per your sample data image, output will be $C$2:$C$4, $C$6:$C$7, $C$9.

Sub Demo()
    Dim ws As Worksheet
    Dim cel As Range, rng As Range, fCell As Range, tempRng As Range
    Dim lastRow As Long
    Dim isNegative As Boolean

    Set ws = ThisWorkbook.Sheets("Sheet2")      'change Sheet2 to your data sheet
    isNegative = False

    With ws
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row    'last row with data in Column C

        For Each cel In .Range("C2:C" & lastRow + 1)  'loop through each cell in Column C
            If cel.Value = -1 Then      'check if value is -1
                If Not isNegative Then
                    Set fCell = cel
                    isNegative = True
                End If
            Else                        'if not then set range
                If isNegative Then
                    If rng Is Nothing Then
                        Set rng = .Range(fCell, cel.Offset(-1, 0).Address)
                    Else
                        Set rng = Union(rng, .Range(fCell, cel.Offset(-1, 0).Address))
                    End If
                    isNegative = False
                End If
            End If
        Next cel
    End With
    For Each tempRng In rng.Areas 'loop through all ranges with -1
        MsgBox tempRng.Address
        'write your code here
    Next tempRng
End Sub

Here's a brief explanation on Booloean variable isNegative used in the code.

isNegative is used to maintain flag whether last cell value in range .Range("C2:C" & lastRow + 1) was -1 or not and is set to FALSE at the beginning. When first -1 is countered then isNegative is set to TRUE and fCell is assigned the corresponding cell address. Till loop encounters -1, isNegative will remain TRUE. When a cell with value not equal to -1 is encountered, range is set using rng from fCell to current cell using Set rng = .Range(fCell, cel.Offset(-1, 0).Address) if only one cell with -1 is encountered else Set rng = Union(rng, .Range(fCell, cel.Offset(-1, 0).Address)) if more than one cell with -1 are encountered.
When second or more successive cells with value not equal to -1 are encountered then nothing happens. Above process is repeated when next cell with value -1 is encountered.

Upvotes: 1

A. Rizvanov
A. Rizvanov

Reputation: 1

I would consider using something like do while flag=-1.

Or instead use the condition if Flag(index)=Flag(index+1) then *do something*, because I think something more complex would require designing a module class.

Upvotes: 0

Cyril
Cyril

Reputation: 6829

You would still loop as normal, but use an If statement to check if column 3 (C) has a -1, such as:

Dim i as Long

For i = 2 to 9 'Assumes 1 is a header row, despite 1-8 being counted
    If Cells(i,3).Value= -1 Then
        'Do something
    End If
Next i

Upvotes: 1

Related Questions