I am running a data cleaning macro that goes to column G, and deletes all the rows which contain 'Y'.

This is neat and fast - however, it depends on having the right data in column G (as this is the range I use in my code). However, I wish for my macro to be a little smarter and more versatile. I'd like for it to go through the first-row, read the values (headers) and find the column with the header 'Opt Out' and then run my delete all rows macro on that column.

That means that even if we add another extra column before column G in our data, the macro should still be able to handle that.

The only slightly feasible answer I managed to find for this was to use the WorksheeetFunction.Match method - however, the problem with this method is that it would not set the whole column where it finds my lookup_value as a range and therefore my macro comes back with an error or doesn't run.

I have read a lot of questions here and other sources but didn't find something that would allow me to define a range like that. Please let me know if my question is not clear.

I am not great with VBA syntax, but quite proficient with Excel and PowerQuery. Please excuse if there is a basic solution to this that I just don't see.

Thank you.

D

' ***************************************************************
' Delete rows based on cell value
'****************************************************************


Sub deleteOptOutRows()

    'Disable certain Excel features whilst the macro is running

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' Declare variables

    Dim deleteRow As String
    Dim ws As Worksheet

    'Set objects
    Set ws = ActiveSheet

        'Loop through the rows of data, in order to delete rows with a Y

        'Y in column G. Our data commences on row 2

        For deleteRows = ws.Range("G" & Rows.Count).End(xlUp).Row To 2 Step -1

            If ws.Range("G" & deleteRows).Value = "Y" Then
                Rows(deleteRows).EntireRow.Delete
            End If

        ' Mode to next cell in the range, which is being looped
        Next deleteRows

    ' Re-enable the Excel features we've disabled at the top of our macro

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True



End Sub

1 Answers

1
Damian On Best Solutions

This is what you need:

Option Explicit
Sub deleteOptOutRows()
    ' ***************************************************************
    ' Delete rows based on cell value
    '****************************************************************

    'Disable certain Excel features whilst the macro is running

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' Declare variables

    Dim i As Long 'use i to count loops
    Dim LastRow As Long, Col As Integer 'add a column and last row variable
    Dim ws As Worksheet

    'Set objects
    Set ws = ActiveSheet
    With ws
        Col = .Cells.Find("Opt Out").Column 'find the column value for that header
        LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row 'fin the last row
        'Loop through the rows of data, in order to delete rows with a Y

        'Y in column G. Our data commences on row 2

        For i = LastRow To 2 Step -1
            If .Cells(i, Col) = "Y" Then
                .Rows(i).EntireRow.Delete
            End If
        ' Mode to next cell in the range, which is being looped
        Next i
    End With
    ' Re-enable the Excel features we've disabled at the top of our macro

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub