VBA to delete range of cells that don’t contain certain text

SJ1981

New Member
Joined
Mar 19, 2019
Messages
4
Hello I have a cell range C5:F34
Each row relates to same record with a different column heading C:F. I would like to be able to clear all cells except for the rows that have “Not Arrived” or “DCase” in column F.
I have made a start with the below however it only deletes the contents of cell F & not C:E aswell? I’m also not sure how to add “DCase” into the criteriacell.Value section, can only get it to work with one or the other.
many thanks for any help that can be given.

TEST Macro
'
Dim criteriarange As Range

Dim criteriacell As Range

Set criteriarange = Range("F5:F34")

For Each criteriacell In criteriarange

If Not criteriacell.Value = "Not Arrived" Then

criteriacell.ClearContents

End If

Next criteriacell

End Sub
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub SJ1981()
   With ActiveSheet
      .Range("C4:F34").AutoFilter 4, "Not Arrived", xlOr, "DCase"
      .AutoFilter.Range.Offset(1).Value = ""
      .AutoFilterMode = False
   End With
End Sub
 

SJ1981

New Member
Joined
Mar 19, 2019
Messages
4
Thank you - this very nearly does the right thing.... it’s the wrong way round though it clears not arrived & DCase rather than everything but these..... so presumably I just need to change the “ “ references to the other options? Thanks
 

SJ1981

New Member
Joined
Mar 19, 2019
Messages
4
I tried changing it to all options except Not arrived & DCase but there was an error as too many arguments?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case how about
Code:
Sub SJ1981()
   With ActiveSheet
      .Range("C4:F34").AutoFilter 4, "<>Not Arrived", xlAnd, "<>DCase"
      .AutoFilter.Range.Offset(1).Value = ""
      .AutoFilterMode = False
   End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top