Macro for Clearing Cels in Multiple Ranges

[jaelei]

Is there a way to create a macro that will ClearContents in multiple ranges within a single worksheet? For instance B2:B3, F2:F3, and B9:D41.

Following a youtube tutorial video I was able to get a single column to work (I am very new macros but it seemed simple enough), however now everything I try generates one of the two following error messages: "Compile Error: Invalid outside procedure" or "Can't execute code in break mode" - even for a single column column now too.

Any help would be greatly appreciated.

[Sisyphus]

Hi,
Let's do this one question at a Time, OK?
you have an invalid outside Procedere in your code which generates a compile error when you attempt to run it. The error causes a Break. Next time you try the same thing you get the other message.
So, in Break mode nothing works except the Debugger. The offending item may be highlighted. Press the Debug button on the error message. Then look at your code. There also is a Help button on the error message and that would explain what an "invalid outside procedure" is.
1. To end the Break mode you should press the Reset button. You find it in the VBE window next to the Continue and Pause buttons (I hope I recall this correctly). These are buttons like you find on a tape recorder. No names until you hover the cursor over them. There are equivalents on one of the drop downs as well. Look for anything that says "Reset".
2. All procedures start with the word Sub or Function and end with End Sub or End Function. Very few items of code are allowed outside of such brackets. So, look for anything written between procedures in your code. Remember that lines of explanatory text must commence with an apostrophe. That marks them as comments and the compiler will not try to interpret them as code.

Now, can you clear the contents of several ranges in one fell swoop? The answer is both, yes and no. Computers work extremely pragmatically, procession one Yes/No information at a time. To clear even a single cell takes a lot of such instructions. Code gives you a user surface to bundle such instructions into a single command. The commands you use are A) to define a range and B) clear it. Hence, if there were a command to define multiple ranges you could clear them all with one ClearContent command. What with the Range being Excel's favorite object there are all kinds of commands to create and define it, including some to define unjoined ranges. The question, therefore, is why should you want to do that? At your level of knowledge you should be happy to clear one range at a time. Like this:

Code: Select all

Sub WipeRange()
    Range("B2:B3").ClearContent
    Range("D5:E6").ClearContent
End Sub


the code required to join the two ranges would be much more voluminous and not faster.
I hope this helps

[jaelei]

I've got it working now, but wasn't sure what or how I'd managed it. Thanks for the explanation; think I get it now.