CODEWITHSUNDEEP
excelvbafor-loopdynamic
farahm
farahm

Reputation: 21

VBA - How to reference columns by header in a "For" statement

I'm writing a code for dynamic, filtered data and I want to reference the columns by the header instead of using "G", "H", etc... My code is supposed to look at the cell in column F (cpass) and then look at the 5 adjacent cells. If those adjacent cells are blank, then the entire row should be deleted, and then it loops to the next cell in column F. My issue is that the columns are dynamic (pulled from a report) and can be out of order on any given day. I can't figure out how to get column references to work in the For statement. Below is the code I attempted to write. Any suggestions would be appreciated!

Sub ClassPassDeleteNEWTEST()                                              
Dim cpass As Integer, fmonth As Integer, init As Integer, lmonth As Integer, piftot As Integer, pifnotax As Integer, LR As Long, r As Long
cpass = Application.WorksheetFunction.Match("Class Pass", Range("A1:AZ1"), 0)
fmonth = Application.WorksheetFunction.Match("First Month Only-", Range("A1:AZ1"), 0)
init = Application.WorksheetFunction.Match("InitiationFee", Range("A1:AZ1"), 0)
lmonth = Application.WorksheetFunction.Match("Last Month Only-", Range("A1:AZ1"), 0)
piftot = Application.WorksheetFunction.Match("PIF Total", Range("A1:AZ1"), 0)
pifnotax = Application.WorksheetFunction.Match("PIF Total No Tax", Range("A1:AZ1"), 0)

LR = Cells(Rows.Count, cpass).End(xlUp).Row

For r = LR To 1 Step -1
    If Range(fmonth & r).Value = "" And Range(init & r).Value = "" And _
       Range(lmonth & r).Value = "" And Range(piftot & r).Value = "" And _
       Range(pifnotax & r).Value = "" Then Rows(r).Delete
Next r


 MsgBox ("All Class Passes with no payment have been deleted, and any with a payment have been cleared.")
      ActiveSheet.Range("A:L").AutoFilter Field:=6

End Sub

Upvotes: 0

Views: 692

Answers (2)

TinMan
TinMan

Reputation: 7759

You should turn off Application.ScreenUpdating and Application.Calculation to improve the speed.

Here is the simplest way:

Sub ClassPassDeleteNEWTEST()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim cpass As Integer, r As Integer

    cpass = Application.WorksheetFunction.Match("Class Pass", Rows(1), 0)

    For r = Cells(Rows.Count, cpass).End(xlUp).Row To 1 Step -1
        If WorksheetFunction.CountA(Rows(r).Columns("H:L")) = 0 Then Rows(r).Delete
    Next r

    ActiveSheet.Range("A:L").AutoFilter Field:=6

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox ("All Class Passes with no payment have been deleted, and any with a payment have been cleared.")

End Sub

It would be better to test if the header exists and fully qualify the ranges to the target worksheet.

Sub ClassPassDeleteNEWTEST()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim cpass As Integer, r As Integer
    With ThisWorkbook.Worksheets(1)
        On Error Resume Next
        cpass = Application.WorksheetFunction.Match("Class Pass", .Rows(1), 0)
        If Err.Number <> 0 Then
            MsgBox "Class Pass header was not found", vbCritical, "Action Cancelled"
            Exit Sub
        End If
        On Error GoTo 0

        For r = .Cells(.Rows.Count, cpass).End(xlUp).Row To 1 Step -1
            If WorksheetFunction.CountA(.Rows(r).Columns("H:L")) = 0 Then .Rows(r).Delete
        Next r

        .Range("A:L").AutoFilter Field:=6
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox ("All Class Passes with no payment have been deleted, and any with a payment have been cleared.")

End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Since the match returns the column index, use Cells references instead of Range references. You're considering the header range A1:AZ1, so the result of the match will be the column index.

Change Range(fmonth & r) to Cells(r, fmonth) and so on.

Upvotes: 3

Related Questions