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.
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.
Any input would be much appreciated!
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!