Help with looping through rows

[mosef]

Hello,
First time poster here hoping if you could answer me a problem I have with an excel spreadsheet,
I have a database with a lot of information (7 to 10k rows) so I can't do this thing manually.
One fo the columns in the database is "year" and it, ideally, should go from 2004 to 2008 (2004, 2005, 2006, 2007, 2008). But I have missing years so, sometimes, it goes: 2004, 2005, 2006 and then jumps back again to 2004 or it jumps straight from 2008 to 2006 and so on.
Now I would like to know if there's a macro that I could use that would, pretty much read row by row the year value and check:
-If the value is 2004, keep it.
-if the next value is 2005, keep it.
-if the next value is 2006, etc.

but, if the sequence breaks at any point, erase those rows completely and start counting again.

I'd be thankful for anyone to help me with this, thanks.

 

[RCBricker]

little confusing.

First when the sequence is broken you want to erase an entire row and then start from the beginning, correct?

why are you erasing? is the data duplication? is there ever a time that the sequence breaks but it is not to be erased?

I am wary of writing a macro that erases data as there is no turning back once it is done. It can be written, but lets be sure that it is what you want.

rich

 

[JackDanIce]

I'd already started on a solution, before Rich's comment, so test this on a copy of your workbook, should it erase any data you want to keep.

This assumes the header of your data is in row 1, the last row is defined by column A and the name of the worksheet is data (you can change this in the code below).

Each time the sequence of 2004, 2005, ... , 2008 is broken, it clears that row of data and restarts the sequence at 2004 for the next row:

Sub Macro1()

    Dim x           As Long
    Dim y           As Long
    Dim z           As Long
    
    Dim ws          As Worksheet
    Dim yearCol     As Long
    Dim yearVal()   As Variant
    
    Dim arr()   As Variant
    
    'Change Data to the sheet name
    Set ws = Sheets("Data")
    yearVal = Array(2004, 2005, 2006, 2007, 2008)
    
    Application.ScreenUpdating = False
    
    With ws
        y = .Cells(1, .Columns.count).End(xlToLeft).Column + 1
        x = .Cells(.rows.count, 1).End(xlUp).row
        yearCol = .Cells(1, 1).Resize(, y).find(what:="Year", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=xlYes).Column

        arr = .Cells(1, 1).Resize(x, y).value

        y = LBound(yearVal, 1)
        
        For x = LBound(arr, 1) + 1 To UBound(arr, 1)
            arr(x, UBound(arr, 2)) = x
            If arr(x, yearCol) = yearVal(y) Then
                y = y + 1
                If y > UBound(yearVal, 1) Then y = LBound(yearVal, 1)
            Else
                For z = LBound(arr, 2) To UBound(arr, 2)
                    arr(x, z) = Empty
                Next z
                y = LBound(yearVal, 1)
            End If
        Next x
            
        With .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2))
            .value = arr
            .Sort key1:=.Offset(, UBound(arr, 2) - 1), order1:=xlAscending, header:=xlYes, Orientation:=xlTopToBottom
            .Offset(, UBound(arr, 2) - 1).Resize(UBound(arr, 1)).ClearContents
        End With
            
     End With
     
     Application.ScreenUpdating = True
     
     Erase arr
     Erase yearVal
     
End Sub

 

[mosef]

@JackDanIce Thank you very much for your input! It does exactly what I need. Thank you very much for the rapid response.

 

[JackDanIce]

You're welcome, glad it's resolved.