Delete Rows if Column ≠ String using Dynamic Columns

nzo12

New Member
Joined
Mar 16, 2016
Messages
5
I created a simple macro that deletes all rows if cell in Column H has a value I'm not interested in. The issue is our data dump sometimes changes without notice, so having static columns normally means having to edit the macro every time there is changes to the source file.

This static code works.

Code:
    Dim ws As Worksheet
    Dim rng As Range
    Dim site As String

    Sheets("Macro Filters").Select    
    site = Range("D4") 'our value based on a filter


    Sheets("Sheet").Select    
    Set ws = ActiveWorkbook.Sheets("Sheet")
    Worksheets("Sheet").UsedRange


    lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row


    Set rng = ws.Range("H1:H" & lastRow) 'this is where I have my issue. I don't want column to be static.


    'filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>" & site
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With


    'turn off the filters
    ws.AutoFilterMode = False

This is what I'm trying to do. The column H is no longer static, and I'm trying to use Find command to find the name of the cell in Row 1, and select the Column. Pass this column into the rng to then delete rows if the cell in that column are not what we're interested in.

Code:
Dim ws As Worksheet
    Dim rng As Range
    Dim site As String

    Sheets("Macro Filters").Select    
    site = Range("D4") 'our value based on a filter


    Sheets("Sheet").Select    
    Set ws = ActiveWorkbook.Sheets("Sheet")
    Worksheets("Sheet").UsedRange


    lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
    
   [B]'''''''''This is where the code is different''''''''''''''[/B]

    'Find column We're interested in, and select this column.
    With Range("A1:A100")
        Set siteCell = .Find(What:="Column Name", LookAt:=xlWhole, MatchCase:=False)
        If Not siteCell Is Nothing Then
            siteColumn = siteCell.Column
        End If
    End With
    
    'Pass column variable into range
    'This is where I'm getting errors
    Set rng = ws.Range(lastRow, siteColumn)


    'filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>" & site
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With


    'turn off the filters
    ws.AutoFilterMode = False

Any input would be much appreciated!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I created a simple macro that deletes all rows if cell in Column H has a value I'm not interested in. The issue is our data dump sometimes changes without notice, so having static columns normally means having to edit the macro every time there is changes to the source file.


This is what I'm trying to do. The column H is no longer static, and I'm trying to use Find command to find the name of the cell in Row 1, and select the Column. Pass this column into the rng to then delete rows if the cell in that column are not what we're interested in.

Code:
Dim ws As Worksheet
    Dim rng As Range
    Dim site As String

    Sheets("Macro Filters").Select    
    site = Range("D4") 'our value based on a filter


    Sheets("Sheet").Select    
    Set ws = ActiveWorkbook.Sheets("Sheet")
    Worksheets("Sheet").UsedRange


    lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
    
   [B]'''''''''This is where the code is different''''''''''''''[/B]

    'Find column We're interested in, and select this column.
    With Range("[B][COLOR=#ff0000]A1:A100[/COLOR][/B]")
        Set siteCell = .Find(What:="[B][COLOR=#ff0000]Column Name[/COLOR][/B]", LookAt:=xlWhole, MatchCase:=False)
        If Not siteCell Is Nothing Then
            [B][COLOR=#ff0000]siteColumn = siteCell.Column[/COLOR][/B]
        End If
    End With
    
    'Pass column variable into range
    'This is where I'm getting errors
    Set rng = ws.[B][COLOR=#ff0000]Range(lastRow, siteColumn)[/COLOR][/B]


    'filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>" & site
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With


    'turn off the filters
    ws.AutoFilterMode = False

Any input would be much appreciated!
If the error is on the Set rng line, its because Range(lastRow, siteColumn) where both arguments are integers is not a legitimate range object. Try using Cells(lastRow, siteColumn) instead.

Beyond that the other lines I marked in red may be problematic too. You said you want to look across Row 1, but the find range is down col A, so if you do find "Column Name" (which doesn't seem to be defined anywhere) it will be in col A and siteCell.Column will always be 1.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top