Excel: beginner VBA question, creating macro to clean range


Hi guys, very new to VBA, and i'm really interested in learning it because I find it fascinating. I know there are plenty of ways to do what i'm trying to do, but i just want to understand what it looks like in VBA so I can start picking up the language. I want to be able to: clean all characters I define from a given range of cells. if i could do it based on the name of the range or even the length/values within the cells in that range, that would be great. heres what I have so far, to give you an idea of what I'm trying to do: Sub removechars() For Each cell In Selection ActiveCell.Value = Substitute(ActiveCell.Value, "+", "") ActiveCell.Value = Substitute(ActiveCell.Value, "(", "") ActiveCell.Value = Substitute(ActiveCell.Value, ")", "") ActiveCell.Value = Substitute(ActiveCell.Value, "-", "") Next cell End Sub I have no clue if this is even remotely on track or if my syntax is correct. When I try to run this, I get a compiler error: sub or function not defined. Again, what i'd like this macro to do eventually is find the proper named column within my worksheet ("Phone", in this case) and clean that range accordingly. Can someone offer any help? Thanks very much in advance :)


This question generated 8 answers. To proceed to the answers, click here.

This thread is current as of August 22, 2014.


For more resources for Microsoft Excel