help with user definded function.

M

MtK

ok I'm really new at the UDF stuff so I need alot of help. here is
what im trying to do. Say I have to columns A and B, now B us
completely full of data and A has data every so often. so it would
look something like this

A B
asdf 1,23,67
23,56
45,45,64
fdsa 78,987
23
fds 32,45

What I want to do is when there is start were there is data in column
A take the data from column B and put it all into one cell until I get
to more data in column A. And continue to do this until there is no
Data in column B.Now a couple of things I would like to happen when
done. If possible I would like the data to be inputed in the first
cell it started in so the first one for my example would be all data
would be in cell "1,23,67". I also would like the seperation between
cell data combing to be a space. I forgot to say each line in the
above example is a different cell. So after it ran it would look like
this


A B
asdf 1,23,67 23,56 45,45,64
fdsa 78,987 23
fds 32,45

thanks for all the responses a macro or UDF would be fine anybody that
can get me started or help with how to do this stuff would be greatly
apperciated. thanks.
 
B

Bob Phillips

You can't do it with a UDF, but you can with a macro

Sub Tidy()
Dim cLastRow As Long
Dim rng As Range
Dim i As Long

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = cLastRow To 2 Step -1
If Cells(i, "A").Value = "" Then
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
" " & Cells(i, "B").Value
If rng Is Nothing Then
Set rng = Cells(i, "A")
Else
Set rng = Union(rng, Cells(i, "A"))
End If
End If
Next i
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

MtK

Thanks Bob. worked perfectly, if you can, can you add a few comments
as im trying to learn how to do this stuff and understand what is
going on in your macro exactly. thanks again.
 
B

Bob Phillips

Sub Tidy()
Dim cLastRow As Long
Dim rng As Range
Dim i As Long

'work out the last row that in column B that contains data
'bys starting at the bottom (Rows.Count), and working
'up (.End(xlUp))
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
'loop backwards from our newly determined last row to
'row 2 (only need row 2 as we are comparing to previous
'and row 1 doesn't have a previous)
For i = cLastRow To 2 Step -1
'first test if column A in the current row is blank, as this
'identifies the data that we want to append to previouys
'row
If Cells(i, "A").Value = "" Then
'then append the data for column B in this row to the
'data in column B for the previous row, separating with
' a space
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
" " & Cells(i, "B").Value
'we won't delete these rows yet, but just save the
'matched cells in a range object. We have to cater
'for the fact that first time, the range object is empty
'so we can't union it, but subsequent times we can
If rng Is Nothing Then
Set rng = Cells(i, "A")
Else
Set rng = Union(rng, Cells(i, "A"))
End If
End If
'and onto the next one
Next i
'all processed, so first check that we have found some
If Not rng Is Nothing Then
'if so delete the entirerows of those cells that we found
'and saved in the range object
rng.EntireRow.Delete
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top