423,850 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

How to create a Macro that runs on a selection of cells

P: 1
OK, So I have a very specific problem that I hope to make not-a-problem, but I need some help. I am a complete neophyte when it comes to making Macros.

Iíve got a customer that has put long lists of references in a single column in Excel (normally, the references are just in one cell). Iíve figured out how to consolidate a selected portion of a column of references into a single cell (see my procedure below), but what Iíd really like to do is to turn this into a Macro that does it instantly.

The problem is that Iíd like to be able to run a Macro on a selection, that is, Iíd like to select the cells and have the macro run on ONLY those cells. Then I could select the cells, run the macro, make another selection, run the macro, etc. This would speed up my work tremendously.

My problem: I canít figure out how to make the Macro run on just a selection. The Macros Iíve recorded only run on the selections Iíve made while recording the Macro. Is there any way to make the Macro generic, so I can select cells, and then run the Macro on them?

Combining References in Excel

1. Letís say the references you want to combine are in E18:E27
2. Make a new column next to the reference designator column, for consolidated references
3. In a blank cell in the new column, where you want to combine all the values, type:
5. Then select the cells that need to be combined
6. The formula will change to =CONCATENATE(TRANSPOSE(E18:E27
7. Donít press enter yet.
8. Click after the last cell reference and hit spacebar
9. Type the & operator
10. Type Ē, Ē (double quote, comma, space, double quote)
11. This will add a comma and a space after every reference
12. Select TRANSPOSE(E18:E27 &", " and press the F9 key (F9 replaces formulas with values)
13. This replaces TRANSPOSE(E18:E27 &" " with its result, {"E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "}
14. Now remove the curly brackets { and }
15. The formula will now look like =CONCATENATE("E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "
16. Select the entire formula, including the = sign, and hit F9
17. Hit Enter
18. Done!

Note: this only works for up to 256 references. Any more and the formula will thrown an error. If there's any way to change it so it will work on more than that, please let me know.
4 Weeks Ago #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,279
This question has been moved to the Excel forum.
4 Weeks Ago #2

Expert 100+
P: 946
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     If Selection.Cells.Count > 256 Then
  3.         MsgBox ("Too much cells selected ...")
  4.     Else
  5.         'Do your stuff here....
  6.     End If
  7. End Sub
You should let the user select the cells, and than run the macro.
It will notify the user if more than 256 cells are selected.
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.