VBA removing a row through lookup and average

Question

I have a table which changes daily. At times the text "AB12" is found in column A in multiples of three. When this happens I need VBA to recognize it has occurred, find the row it has occurred on and then average the values in columns C,E,G,I,K,M,O,and Q on that row. This average then needs to be compared to the average for the next row and the row after that deleting all but the row with the highest average.

The code I have written thus far is found below.

Sub FindAB12()
    Columns("A:Q").Select
    Set rngRange = Selection.CurrentRegion
    lngNumRows = rngRange.Rows.Count
    lngFirstRow = rngRange.Row
    lngLastRow = lngFirstRow + lngNumRows - 1
    lngCompareColumn = ActiveCell.Column
    For lngCurrentRow = lngLastRow To lngFirstRow Step -1
        If (Cells(lngCurrentRow, lngCompareColumn).Text = "AB12" And Cells(lngCurrentRow + 1, lngCompareColumn).Text = "AB12") Then _
            AB121 = Application.WorksheetFunction.Average(Sheet1.Range("C" & lngCurrentRow & ":Q" & lngCurrentRow))
            AB122 = Application.WorksheetFunction.Average(Sheet1.Range("C" & (lngCurrentRow + 1) & ":Q" & (lngCurrentRow + 1)))
            AB123 = Application.WorksheetFunction.Average(Sheet1.Range("C" & (lngCurrentRow + 2) & ":Q" & (lngCurrentRow + 2)))
        Next lngCurrentRow

        If AB1211 > AB122 And AB1211 > AB123 Then
            Rows(lngCurrentRow + 1 And lngCurrentRow + 2).Delete
        ElseIf AB122 > AB123 And AB122 > AB121 Then
            Rows(lngCurrentRow And lngCurrentRow + 2).Delete
        ElseIf AB123 > AB122 And AB123 > AB121 Then
            Rows(lngCurrentRow And lngCurrentRow + 1).Delete
        End If
    End Sub

In a separate module I am having the sub called upon opening the tab. It would be expected that upon opening this tab only a single row called AB12 would exist in sequential rows (though another AB12 could exist in a later row). The code works up until I try to average (I have used the same code to successfully find and delete these duplicated rows when calculation was not needed). When it plays and hits the second average function I get the error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed. I am fairly new to VBA and after looking at a lot of code that others have created I have not been able to solve the problem. I hope someone out there can help. I have never used this forum before and am not sure how to best upload my current code, hope my problem and current code is clear.

All of your average ranges are missing a colon: `Application.WorksheetFunction.Average(Sheet1.Range("C" & lngCurrentRow & ":Q" & lngCurrentRow))`. Additionally, I'd highly suggest putting `Option Explicit` at the top of your module, `Dim`ing your variables and using more easily understandable variable names :) — dwirony, May 22 '19 at 15:32
Also recommended reading [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) on how to avoid using Select — Damian, May 22 '19 at 15:43
I have edited your code to remove the empty lines, and also to indent your code. Notice how much more readable it is. — Ron Rosenfeld, May 22 '19 at 15:46
`lngCurrentRow And lngCurrentRow + 1` In VBA, `AND` when used like this (comparing two numeric values) will output a bitwise comparison. So, for example, if `lngCurrentRow=5`, this formula will evaluate to `4`; probably not what you want. — Ron Rosenfeld, May 22 '19 at 15:51

score 0 · Accepted Answer · answered May 22 '19 at 15:54

I took a shot at cleaning this up for you here - I deleted some unnecessary variables, removed Select/ActiveColumn and fixed some typos which you had (those were easily found by using Option Explicit):

Option Explicit
Sub FindAB12()

    Dim lngLastRow As Long, lngCompareColumn As Long, lngCurrentRow As Long
    Dim AB121 As Long, AB122 As Long, AB123 As Long

    lngCompareColumn = 1 'Column A
    lngLastRow = Cells(Rows.Count, lngCompareColumn).End(xlUp).Row

    For lngCurrentRow = lngLastRow To 2 Step -1

        If Cells(lngCurrentRow, lngCompareColumn).Value = "AB12" And _
           Cells(lngCurrentRow + 1, lngCompareColumn).Value = "AB12" And _
           Cells(lngCurrentRow + 2, lngCompareColumn).Value = "AB12" Then
            AB121 = Application.WorksheetFunction.Average(Sheet1.Range("C" & lngCurrentRow & ":Q" & lngCurrentRow))
            AB122 = Application.WorksheetFunction.Average(Sheet1.Range("C" & lngCurrentRow + 1 & ":Q" & lngCurrentRow + 1))
            AB123 = Application.WorksheetFunction.Average(Sheet1.Range("C" & lngCurrentRow + 2 & ":R" & lngCurrentRow + 2))

            If AB121 >= AB122 And AB121 >= AB123 Then
                Rows(lngCurrentRow + 2).Delete
                Rows(lngCurrentRow + 1).Delete
            ElseIf AB122 >= AB123 And AB122 >= AB121 Then
                Rows(lngCurrentRow + 2).Delete
                Rows(lngCurrentRow).Delete
            ElseIf AB123 >= AB122 And AB123 >= AB121 Then
                Rows(lngCurrentRow + 1).Delete
                Rows(lngCurrentRow).Delete
            End If

        End If

    Next lngCurrentRow

End Sub

Thank you so much for all the help. After hammering at it for a while I just hit a mental road block. This new code doesn't return any errors however when I make the average of the row associated with AB121 clearly greater then the average of the others it still deletes the rows associated with AB121 and AB122 keeping the row associated with AB123. Thoughts? — NightSerpent, May 22 '19 at 16:51
There are text in those columns. To be a bit more clear about the problem I am attempting to format a machine output file and these boxes specify the units of the number generated in the previous column. I attempted to delete this text and see if that fixed the issue, it did not. — NightSerpent, May 22 '19 at 16:59
@NightSerpent Can you post a screenshot of some sample data? Testing on my end worked fine. — dwirony, May 22 '19 at 17:03
dwirony - I am not sure what occurred. When I simplified my spreadsheet to show you, the program worked properly. I am going to have to keep playing to determine if there is any issue. Thank you for all the help. — NightSerpent, May 22 '19 at 17:52

VBA removing a row through lookup and average

1 Answers1