vba - VB Code to remove HTML code keeps crashing excel for ~40k dataset -

this vba code:

sub removetags() dim r range selection.numberformat = "@"  'set cells text numberformat createobject("vbscript.regexp")    .pattern = "\<.*?\>"    .global = true each r in selection     r.value = .replace(r.value, "")    next r end end sub 

it remove markup language cells crashes when run ~40k records. there wrong code or should change excel settings?

my guess excel craches while trying write text cell. here couple of things can try:

• use .value2 rather .value work raw value.
• add single quote in front of text. won't appear, ensure text format
• use not followed pattern rather non greedy sure handle line breaks.

sub removetags()     dim values(), r long, c long, re object      ' load values in array     values = selection.value2      ' create regex     set re = createobject("vbscript.regexp")     re.pattern = "<[^>]*>"     re.global = true      ' remove tags each value     r = 1 ubound(values, 1)         c = 1 ubound(values, 2)             values(r, c) = "'" & re.replace(values(r, c), vbnullstring)         next     next      ' write values sheet     selection.value2 = values end sub