Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2003
    Location
    Creston, Br. Columbia
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging rows and preserving the data (Excel 2000)

    Is there a way to merge adjacent pairs of rows in a spreadsheet, while preserving the data (combining it) and preserving the columns? .. All the data is text. ... I have hunted through the help files for Excel and can't seem to find anything helpful. ... But then, maybe it's not possible.

    Al

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 2000)

    Can you copy a few rows of data to a new workbook and then show what the desired results would be after the merge. (Alter any sensitive data first.)
    Then post this workbook to this forum

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merging rows and preserving the data (Excel 2000)

    I am not sure exaclty what you are after. Does this do it (Test it on a copy of your spreadsheet as the data will be changed)

    <pre>Option Explicit
    Sub CombineMerge()
    Dim rng As Range
    Dim rCell As Range
    Dim iCols As Integer
    Dim iCol As Integer
    Dim lRows As Long
    Dim lRow As Long
    Set rng = Selection
    With rng
    iCols = .Columns.Count
    lRows = .Rows.Count
    End With

    For lRow = 1 To lRows Step 2
    For iCol = 1 To iCols
    Set rCell = rng.Cells(lRow, iCol)
    With rCell
    .Value = .Value & _
    .Offset(1, 0).Value
    .Offset(1, 0).ClearContents
    Range(rCell, rCell.Offset(1, 0)).Merge
    End With
    Next
    Next

    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    Add this into a module, select the cells to combine and merge and then run the code
    Steve

  4. #4
    3 Star Lounger
    Join Date
    Sep 2003
    Location
    Creston, Br. Columbia
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    Okay ... I'll give this a try ... it's all dummy data in the same row-column arrangement. There are more than 200 total rows generally.

    Lines 1 to 10 indicate what it looks like (sort of) at first.
    Lines 17 to 21 show what the combined data should look like.

    It looks like it should be simple ... but ...

    Al

  5. #5
    3 Star Lounger
    Join Date
    Sep 2003
    Location
    Creston, Br. Columbia
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    Thanks Steve ... for the extra effort from you. I'll give this a try. I don't have much experience with Excel macros.

    Al

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merging rows and preserving the data (Excel 20

    Based on your example, this seems more of what you want, no merging just combining the second row in col B with the first row and keeping all the others as is, tehn deleting the rows that were combined.

    Steve

    <pre>Option Explicit
    Sub CombineRows()
    Dim rng As Range
    Dim lRows As Long
    Dim lRow As Long
    Set rng = Selection
    lRows = rng.Rows.Count

    For lRow = lRows To 1 Step -2
    With rng.Cells(lRow - 1, 2)
    .Value = .Value & " " & _
    .Offset(1, 0).Value
    .Offset(1, 0).EntireRow.Delete
    End With
    Next

    Set rng = Nothing
    End Sub</pre>


  7. #7
    3 Star Lounger
    Join Date
    Sep 2003
    Location
    Creston, Br. Columbia
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    Okay ... thanks Steve ... I'll give this a try ... and let you know how it works out.

    Al

  8. #8
    3 Star Lounger
    Join Date
    Sep 2003
    Location
    Creston, Br. Columbia
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    Once again ... THANKS. That did exactly what I needed ... and saves me hours of work.

    Al

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    Would it be possible to modify the code so it can merge and retain the data on cells such as the ones in color on the attached workbook? That would save me hours of time as well!

    Thanks.
    Pooja

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merging rows and preserving the data (Excel 20

    Does this do what you want?

    Sub MergeCellsInColumnC()
    Dim rng As Range
    Dim strVal As String
    Dim lngRow As Long
    Dim lngMaxRow As Long
    lngMaxRow = Range("B65536").End(xlUp).Row
    Set rng = Range("B30").MergeArea
    Application.DisplayAlerts = False
    Do
    Set rng = rng.MergeArea
    If rng.MergeCells Then
    strVal = ""
    For lngRow = 1 To rng.Rows.Count
    strVal = strVal & Trim(rng.Cells(lngRow, 2)) & " "
    Next lngRow
    With rng.Offset(0, 1).Resize(rng.Rows.Count, 1)
    .Merge
    .Value = Trim(strVal)
    End With
    End If
    Set rng = rng.Offset(1, 0)
    Loop Until rng.Row > lngMaxRow
    Application.DisplayAlerts = True
    End Sub

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging rows and preserving the data (Excel 20

    It sure does! Thank you!

    Pooja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •