Searches for an item in a column, then if found, delete that cell (not clear, not full row, just delete cell with Shiftup)
We can tell it to delete more than 1 column, change Cols2Delete
DeleteAllOccurances will delete all cells with that item, keep doing that until that item no longer found in that col
Function MatchIf_ThenDelete(SearchFor, InCol, Optional Cols2Delete = 1, Optional DeleteAllOccurances = 0, _
Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
' Searches for an item in a column, then if found, delete that cell (not clear, delete with Shiftup)
' We can tell it to delete more than 1 column, change Cols2Delete
' Uses and needs MatchIf
' DeleteAllOccurances will delete all cells with that item, keep doing that until that item not found in that col
'
Deleted = 0
LastOne = StartFromRow
Do
RowFo = MatchIf(SearchFor, InCol, Wb, Shee, LastOne)
If RowFo = 0 Then GoTo ByeBye
If Cols2Delete = 0 Then Exit Do
' Actually delete it
Rang = Range(InCol & RowFo).Address ' we need to actually delete
If Cols2Delete > 1 Then Rang = Rang & ":" & Range(InCol & RowFo).Offset(, Cols2Delete - 1).Address
Workbooks(Wb).Worksheets(Shee).Range(Rang).Delete xlShiftUp
Deleted = Deleted + 1
If DeleteAllOccurances = 0 Then Exit Do
If LastOne > 1 Then LastOne = RowFo - 1 ' to make sure we are searching and deleting everything
Loop
ByeBye:
MatchIf_ThenDelete = Deleted
End Function
Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
' Searches for an item in a column, then if found, delete that cell (not clear, delete with Shiftup)
' We can tell it to delete more than 1 column, change Cols2Delete
' Uses and needs MatchIf
' DeleteAllOccurances will delete all cells with that item, keep doing that until that item not found in that col
'
Deleted = 0
LastOne = StartFromRow
Do
RowFo = MatchIf(SearchFor, InCol, Wb, Shee, LastOne)
If RowFo = 0 Then GoTo ByeBye
If Cols2Delete = 0 Then Exit Do
' Actually delete it
Rang = Range(InCol & RowFo).Address ' we need to actually delete
If Cols2Delete > 1 Then Rang = Rang & ":" & Range(InCol & RowFo).Offset(, Cols2Delete - 1).Address
Workbooks(Wb).Worksheets(Shee).Range(Rang).Delete xlShiftUp
Deleted = Deleted + 1
If DeleteAllOccurances = 0 Then Exit Do
If LastOne > 1 Then LastOne = RowFo - 1 ' to make sure we are searching and deleting everything
Loop
ByeBye:
MatchIf_ThenDelete = Deleted
End Function
SearchFor, InCol, Optional Cols2Delete, Optional DeleteAllOccurances, Optional Wb, Optional Shee, Optional StartFromRow
Views 3,202
Downloads 1,276
CodeID
DB ID
