Pick out cells in column

[WCE_123]

Hi,

I would like to pick out all cells in column A that has a number in it, then show all the numbers in cell C6 as 18,3,2,1

A B C
1 18
2
3 3
4
5 2
6 1 18,3,2,1

Cheers

Colin

[Kyle123]

as a VBA function:

Please Login or Register  to view this content.

[WCE_123]

Hi Kyle 123,

This will be be my first attempt at VBA function any tips !!!!

Cheers

Colin

[royUK]

Copy the Kyle's code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

In any cell type

=JoinNumbers(A1:A5)

This will check A1 to A5

[Kyle123]

Thanks Roy

[royUK]

You provided the code.

Do you have long legs or commute between Sheffield & Leeds?

[Kyle123]

haha I'd need pretty long legs! I commute to Sheffield everyday - only takes about 30mins though

This is a bit more resilient as it allows for doing the same thing along a row:

Please Login or Register  to view this content.

[royUK]

I worked in Sheffield until earlier this year, it could take longer than 30 minutes to escape some days

[Kyle123]

I work behind don valley stadium so it's not too bad, straight on the M1

[WCE_123]

Hi,

Both work well, but I am getting 59,,,65,,,,73,,,79,,,,,,,
aditional comma's where the cell is blank is it possible to get rid of these ?

Cheers

Colin

[WCE_123]

Hi - Do you think it's possible to get rid of additional commas ?

[Kyle123]

Give this a whirl:

Please Login or Register  to view this content.