Find Non-blank cells, replace with x

[delecto]

Hi all,

I have worksheet with a large pivot table, and a button for users to push to "export" the sheet into a new workbook and paste as values (to make the file smaller). Part of what the code does is find all the numbers in the data part of the pivot table (or rather, where the PT was since it's now values), and replaces them with Xs. The find/replace code works ok, but (feel liks it) takes a long time to run. Here is what I have:

Sub ReplaceNumbers()
  Dim c As Range
For Each c In Range("d12:dx500")
 If c <> "" Then
 c = "X"
 End If
Next c
End Sub

Is this the most efficient way to do this? Or is there something spiffier/faster? Thanks!
- Jill

 

[Gerald Higgins]

Here's a possibility.
You don't need a macro for it, but you could probably code it just as well.

Insert the letter X into some cell (that's outside your target range).
Copy it.
Select your target range.
Hit f5 to bring up the GOTO dialog box.
Hit the "Special" button.
If your data is all just values (not formulas) then tick the option for Constants.
Tick or untick options as required for Numbers, Text and so on.
This should highlight all data in your target range, and leave empty cells unhighlighted.
OK that, then Paste.

Works for 2003.

 

[Sgt Rock]

Hi all,

I have worksheet with a large pivot table, and a button for users to push to "export" the sheet into a new workbook and paste as values (to make the file smaller). Part of what the code does is find all the numbers in the data part of the pivot table (or rather, where the PT was since it's now values), and replaces them with Xs. The find/replace code works ok, but (feel liks it) takes a long time to run. Here is what I have:

Sub ReplaceNumbers()
  Dim c As Range
For Each c In Range("d12:dx500")
 If c <> "" Then
 c = "X"
 End If
Next c
End Sub

Is this the most efficient way to do this? Or is there something spiffier/faster? Thanks!
- Jill

Suggestion Jill
I used this code to clear out text from B2:K601 in the blink of an eye and replaced with an X - the only difference is I added after the Dim statement

Application.ScreenUpdating = False

and after before

End Sub

I turned it back on with

Application.ScreenUpdating = True

Hope you find useful?