run through a column and copy the cells that aren't blank

  • Thread starter Thread starter Homey
  • Start date Start date
H

Homey

I have a worksheet with cells in one column being populated as
invoices come due. I would like to have a formula that takes the
values from the cells that are populated and set them up side by side
with a space.

ie

a1 = 100
a2 = blank
a3 = blank
a4 = 105
a5 = blank
a6 = 111
a7 = 113

I would like cell Z1 to have the following value. 100, 105, 111, 113

Thanks for the help.

-Darren
 
try this

Sub putemtogether()
For i = 8 To 1 Step -1
If IsNumeric(Cells(i, 1)) Then mylist = Cells(i, 1) & ", " & mylist
Next
[f1] = Left(mylist, Len(mylist) - 4)
End Sub
 
I can't believe noone had an answer for me on this. I decided to make
a UDF to do what I was asking. Thanks anyway.
 
Here is the UDF that works for me.

Function CountValue(rSumRange As Range)

Dim rCell As Range
Dim vResult
Dim tempNum

For Each rCell In rSumRange
If rCell.Value <> 0 Then
If tempNum = 0 Then
tempNum = 1
vResult = vResult + rCell.Value
Else
vResult = vResult + ", " + rCell.Value
End If
End If
Next rCell

CountValue = vResult
End Function
 
Back
Top