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
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
as a VBA function:
Public Function JoinNumbers(rng As Range) As String JoinNumbers = Replace(Join(Application.Transpose(rng), ","), ",,", ",") End Function
Hi Kyle 123,
This will be be my first attempt at VBA function any tips !!!!
Cheers
Colin
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
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Thanks Roy
You provided the code.
Do you have long legs or commute between Sheffield & Leeds?
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:
Public Function JoinNumbers(rng As Range) As String On Error GoTo oneD If Not IsError(UBound(Application.Transpose(rng), 2)) Then JoinNumbers = Replace(Join(Application.Transpose(Application.Transpose(rng)), ","), ",,", ",") End If Exit Function oneD: JoinNumbers = Replace(Join(Application.Transpose(rng), ","), ",,", ",") End Function
Hi - Do you think it's possible to get rid of additional commas ?
I worked in Sheffield until earlier this year, it could take longer than 30 minutes to escape some days
I work behind don valley stadium so it's not too bad, straight on the M1
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
Give this a whirl:
Public Function JoinNumbers(rng As Range) As String Dim s If rng.Columns.Count > 1 Then s = Application.Transpose(Application.Transpose(rng)) Else s = Application.Transpose(rng) End If s = Split(Join(s, ",|") & ",", "|") For x = LBound(s) To UBound(s) If Len(s(x)) > 1 Then JoinNumbers = JoinNumbers & s(x) Next x JoinNumbers = Left(JoinNumbers, Len(JoinNumbers) - 1) End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks