MS Excel VBA find cell with string value

Question

I am having a hard time figuring out the best way to find and replace a string value in a column of over 4,000 rows where the value should be numeric.

The customer collects several workbooks and wants to consolidate them. I have that working but one particular column should be a numeric values but occasionally the reporting system will dump out a extraneous string character in that column.

Here is what I am doing right now with VBA

For Each r in rng
if not IsNumeric(r.value2) then r.value=null
Next r

As you can imagine, this for/next loop takes a while to run. There has got to be a better way to deal with this. Any help would be appreciated.

Thank you, Fred

PS: I have toyed with .Find and .Replace but couldn't make them work.

I just tried this on 20k rows and it ran quite quickly. Is there something else going on in the code? — mrbungle
– mrbungle, Commented May 8, 2015 at 18:36
@mrbungle The code provided is the exact code. Nothing else in between. I am running it from Access, I don't think this would be causing a time issue. — acr_scout
– acr_scout, Commented May 8, 2015 at 18:38

nutsch · Accepted Answer · 2015-05-08 18:25:19Z

3

try to use the specialcells feature

on error resume next 'in case there are no string cells
rng.specialcells(xlCellTypeConstants,2).clear
on error goto 0

answered May 8, 2015 at 18:25

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nutsch Over a year ago

Specialcells and autofilter are two great tools to avoid loops. Saves quite a bit of processing time occasionally.

brettdj Over a year ago

Good to see you back Thomas.

nutsch Over a year ago

Life's been busy, but I do need to keep my excel skills challenged.

Collectives™ on Stack Overflow

MS Excel VBA find cell with string value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related