470,575 Members | 1,347 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

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

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.
Nov 12 '18 #1
2 3628
12,516 Expert Mod 8TB
This question has been moved to the Excel forum.
Nov 13 '18 #2
1,044 Expert 1GB
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.
Nov 17 '18 #3

Post your reply

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

Similar topics

By using this site, you agree to our Privacy Policy and Terms of Use.