Merging two cells within the same table

[Phia]

I have a spreadsheet of data that I export from a database. The problem is
that Excel interprets each data row as a hard return, instead of keeping that
test together. Example:

name address phone number
Jill 555 1212 555-555-555
-- Palasades dr.

Instead of keeping the address in once cell, it splits it into two rows.
When I do a sort, all the data is then moved out of context and is valid.
What I am trying to do is Merge the two rows, Palasades dr. and 555 1212 into
one cell. When I try to do this manually, the Palasades dr. text is deleted.


I've found scripts that merge cells by columns, but none that merge by rows.
Any help would be wonderful!
Think of all the beauty still around you and be happy-Anne Frank

Sophia

 

[Joel]

I'm assuming there are two rows of data for every line. If not how can I
tell when there are two line or one line for each entry. Your posting had --
in column A. iI this really true? If ther are two line for every entry then
use code below.

Sub CombineRows()

RowCount = 1
Do While Range("B" & RowCount) <> ""
NextAddress = Trim(Range("B" & (RowCount + 1)))

Range("B" & RowCount) = _
Range("B" & RowCount) & NextAddress
Rows(RowCount + 1).Delete
RowCount = RowCount + 1
Loop
End Sub

 

[Phia]

Hi Joel, thanks for your reply. Sorry its taken so long to get back, I've
been swamped at work. Here is what my data looks like.
-------------------------------------------------------------------------------------------
ES - If a report from 3.0 is moved into 3.1, new express analysis and
reports cannot be created.
------------------------------------------------------------------------------------------
1- A new report can be created. A new express analysis report can be created.
-------------------------------------------------------------------------------------------
2- There is a script to put the 2 Spanish reports into the current
environment (3.1 from Prognoz.) The 2 reports were written in 3.0.
-------------------------------------------------------------------------------------------
3- As soon as those reports are restored or moved forward to the next
version, new reports and express analysis cannot be created.
--------------------------------------------------------------------------------------------

So, steps 1, 2 and 3 need to be merged into the first row so they are all in
one cell not 4 seperate cells on 4 rows.

I did try your script and it didn't seem to work. What I currently do
manually is enter HRTs in the top cell, then cut and paste the cells in the
following rows into the upper most cell-which is very time consuming.

I haven't found a way to merge two cells vertically.

Thanks!

 

[Joel]

Try this code. I'm assuming all the data is in column A. I'm also using the
first character in each cell to determine which row s to combine and which
don't get combined. If the first character in the next row is a numeric
digit I combine the current row with the next rowrows. Otherwise I don't
combine two rows.

Sub CombineRows()

RowCount = 1
'loop until next row doesn't have any data
Do While Range("A" & (RowCount + 1)) <> ""
'get the first character in the next row. Ignore spaces
FirstChar = Left(Trim(Range("A" & (RowCount + 1))), 1)
'test 1st character in next row if it is a digit 0 - 9
If IsNumeric(FirstChar) Then
'get the text in the next row
NextLine = Range("A" & (RowCount + 1)).Text
'combine two rows adding a line return between the two strings
Range("A" & RowCount) = Range("A" & RowCount) & _
Chr(10) & NextLine
'delete next row
Rows(RowCount + 1).Delete
Else
'don't combine rows - skip
RowCount = RowCount + 1
End If
Loop

End Sub