Macro question
I have the following macro that'll take a cell in one worksheet, and if
found, delete the row from a second worksheet.
I want to modify it though, to pull in a 3rd worksheet, and replace the
contents of the source data with the new one. If it's in the
corrections worksheet, I want to replace the entire contents of the
cells in the destination.
How do I do that with this code?
'Criteria range on a different sheet
'Example with the criteria range on a different sheet
'The example below filter C1:C? In a sheet named “DL-Rebate”.
'And use as criteria all the cells in column A of a sheet named "rebate".
'Note: You can use also wildcards like *food* or *store if you want.
Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Correct Addresses")
Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With
'Loop through the cells in the Criteria range
For Each cell In CriteriaRng
With Sheets("Direct & Indirect")
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=cell.Value
With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'---If Not rng Is Nothing Then rng.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
Next cell
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
|