Apply to selected columns

[kmlloyd]

I'm using the following code to fill in blanks in a table. The table has anywhere from 1-5 columns with blanks I need to fill. Currently the code only fills in one column at a time. How do I change it so I can select a number of columns and only run the macro once?

Please Login or Register  to view this content.

[shg]

Where is the table located?

[kmlloyd]

It's on one worksheet, anywhere from 3-8 columns and up to 500 rows. Not sure if that answers your question...

[shg]

Is it the entire used range of the sheet?

[kzmr1234]

Much easier way than a macro.
Highlight your whole area
-Edit-GoTo (or Control G)
-Special
-choose Blanks (all blanks are highlighted)
Then put in what ever you want in the blanks (n/a, or whatever) and instead of pressing enter press Control Enter which will fill all blanks.

[kmlloyd]

Much easier way than a macro.
Highlight your whole area
-Edit-GoTo (or Control G)
-Special
-choose Blanks (all blanks are highlighted)
Then put in what ever you want in the blanks (n/a, or whatever) and instead of pressing enter press Control Enter which will fill all blanks.

That's what I have been doing...but when I try to train that to my folks who are not so comfortable with computers, they don't understand. I was thinking it might be easier if I had a button with the macro attached to it and all they'd have to do is highlight the columns they wanted and click the button...

[kmlloyd]

Is it the entire used range of the sheet?

No...it's always the first column, and usually the second and third...sometimes others, but the last 2 columns don't have any blanks.

[kmlloyd]

Ok so i just recorded a macro of the Edit > GoTo function...I don't know if that's the same thing as the macro I had copied, but it seems to work....

[shg]

My question is, how is the code supposed to know the extents of the table?

You can apply the method to the selection, or the current region of the selection, or the current region of some particular cell that's always in the table, or to a named range, or to the used range, or to ....

[kmlloyd]

My question is, how is the code supposed to know the extents of the table?

You can apply the method to the selection, or the current region of the selection, or the current region of some particular cell that's always in the table, or to a named range, or to the used range, or to ....

Yes, I guess I was picturing it applying to a selection...which is what I thought this code was doing, but it will only do one column at a time, even if I have multiple columns selected. I'm really new to VBA (is it obvious???) so I don't know how to read it to troubleshoot.

[kzmr1234]

As long as the cells were highlighted before the macro was run...and...your people know to highlight their cells in the area before running the macro.

[shg]

Try this:

Please Login or Register  to view this content.