Find Non-blank cells, replace with x

delecto

New Member
Joined
Mar 3, 2009
Messages
15
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:
Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Aug 6, 2010
Messages
12
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:
Code:
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
Code:
Application.ScreenUpdating = False
and after before
Code:
End Sub
I turned it back on with
Code:
Application.ScreenUpdating = True
Hope you find useful?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,833
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top