code to clear contents of cell except for cells with x

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,548

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #2

What do you have in those cells values, or formula?

 

Upvote 0

Victtor

Board Regular

Joined

Jan 4, 2007

Messages

170

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #3

Values

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,548

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #4

How about

Code:

Sub Keepx()

   With Range("F5:AG33")
      .Replace "x", "=x", xlWhole, , False, , False, False
      .SpecialCells(xlConstants).ClearContents
      .Replace "=x", "x", xlWhole, , False, , False, False
   End With
End Sub

 

Upvote 0

H

Haluk

Rules Violation

Joined

Oct 26, 2002

Messages

1,075

• Feb 4, 2018

• #5

Try this, if it helps ...

Code:

    For Each cell In Range("F5:AG33")
        If LCase(cell) <> "x" Then cell.ClearContents
    Next

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,465

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #6

Here is another macro (a one-line) that should also do what you asked for...

Code:

Sub KeepXes()
  [F5:AG33]=[IF(F5:AG33="x","x","")]
End Sub

 

Upvote 0

Victtor

Board Regular

Joined

Jan 4, 2007

Messages

170

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #7

Thanks everyone,

I tried all three and all of them worked!!

Again Thanks

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,548

Office Version

1. 365

Platform

1. Windows

• Feb 4, 2018

• #8

Glad we could help & thanks for the feedback

 

Upvote 0