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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,194
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,026
Messages
5,835,024
Members
430,332
Latest member
Charly_Moon

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