CODEWITHSUNDEEP
excelexcel-2010vba
user2378895
user2378895

Reputation: 431

Delete row offset by 1

I am trying to write a macro that loops through a set of rates that has 3 columns: rate_table, rate_date, rate

Here's what I have so far. I think I just need the command to delete the row

Private Const RATE_TABLE_COL = 1
Private Const RATE_DATE_COL = 2
Private Const RATE_COL = 3

Sub RemoveDuplicateRates()
    Dim iLastRow As Long, iRow As Long, sThisRate As String, sPrevRate As String

    'Find the last row (in column A) with data.
    iLastRow = shtSource.Range("A:A").Find("*", searchdirection:=xlPrevious).Row

    If iLastRow < 2 Then
        MsgBox "No data to process!", vbCritical
        Exit Sub
    End If

    sThisRate = ""
    sPrevRate = ""

    For iRow = iLastRow To 1 Step -1
        sPrevRate = sThisRate
        sThisRate = Cells(iRow, 1)
        If sThisRate = sPrevRate Then
            If Cells(iRow, RATE_COL) = Cells(iRow - 1, RATE_COL) Then
                ' need code here to delete row offset by 1 from iRow
            End If
        End If
    Next iRow
End Sub

Upvotes: 2

Views: 1541

Answers (1)

user2378895
user2378895

Reputation: 431

Here's final working code:

Private Const RATE_TABLE_COL = 1
Private Const RATE_DATE_COL = 2
Private Const RATE_COL = 3

Sub RemoveDuplicateRatesRev2()
    Dim iLastRow As Long, iRow As Long, sThisRate As String, sPrevRate As String, shtSource As Worksheet

    Set shtSource = ActiveSheet

    'Find the last row (in column A) with data.
    iLastRow = shtSource.Range("A:A").Find("*", searchdirection:=xlPrevious).Row

    If iLastRow < 2 Then
        MsgBox "No data to process!", vbCritical
        Exit Sub
    End If

    sThisRate = ""
    sPrevRate = ""

    For iRow = iLastRow To 1 Step -1
        sPrevRate = sThisRate
        sThisRate = shtSource.Cells(iRow, 1)
        If sThisRate = sPrevRate Then
            If shtSource.Cells(iRow, RATE_COL) = shtSource.Cells(iRow + 1, RATE_COL) _
                And shtSource.Cells(iRow, RATE_DATE_COL) < shtSource.Cells(iRow + 1, RATE_DATE_COL) Then
                ' need code here to delete row offset by 1 from iRow
                shtSource.Cells(iRow + 1, RATE_COL).EntireRow.Delete
            End If
        End If
    Next iRow
End Sub

Upvotes: 2

Related Questions