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

[WhiteLancer64]

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:
4. =CONCATENATE(TRANSPOSE(
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.

[Rabbit]

This question has been moved to the Excel forum.

[Luuk]

Sub Macro1()
    If Selection.Cells.Count > 256 Then
        MsgBox ("Too much cells selected ...")
    Else
        'Do your stuff here....
    End If
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.