Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge columns/hide rows (Excel 2007)

    Hello, I am trying to help the operations/mainframe area with output from test scanning software. See below. (I also attached a sample of their spreadsheet)

    Columns C, D, and E need to be merged together. Also, this request is minor and not as important, would it be possible to hide the odd rows so there is no wasted space? Thank you so much for your help! We appreciate it greatly!
    Attached Files Attached Files

  2. #2
    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: Merge columns/hide rows (Excel 2007)

    You can add to a column:
    =C2&D2&E2
    and copy it down the column to merge them.

    If you select that column and enter Data-filter - autofilter, you can select the dropdown list for this new column and select "non-blanks" to hide the blank rows

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge columns/hide rows (Excel 2007)

    Thank you - I was able to merge the columns and hide the blank rows. How would this be accomplished for all of the results from the test scanning software. Would a macro do this or would someone have to manually enter a new column and insert the formula and then filter?

    Melanie

  4. #4
    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: Merge columns/hide rows (Excel 2007)

    It can be done manually as I indicated. A macro could actually combine CDE into a new column and delete the original ones if desired and then filter blank rows as well.

    <pre>Option Explicit
    Sub MergeFilter()
    Dim lLastRow As Long
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Dim lRow As Long
    Dim iCol As Integer
    Dim i As Integer
    Dim sText As String
    iCol = 3 'Col C
    With wks
    .Columns(iCol).Insert
    lLastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For lRow = 2 To lLastRow
    sText = "'"
    For i = 1 To 3
    sText = sText & .Cells(lRow, iCol + i)
    Next
    .Cells(lRow, iCol) = sText
    Next
    .Cells(1, iCol) = "Merge"
    For i = 1 To 3
    .Columns(iCol + 1).EntireColumn.Delete
    Next
    .Columns(iCol).AutoFilter Field:=1, Criteria1:="<>"
    End With
    Set wks = Nothing
    End Sub</pre>


    You could also just delete the blank rows instead:

    <pre>Option Explicit
    Sub MergeDelete()
    Dim lLastRow As Long
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Dim lRow As Long
    Dim iCol As Integer
    Dim i As Integer
    Dim sText As String
    iCol = 3 'Col C
    With wks
    .Columns(iCol).Insert
    lLastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For lRow = 2 To lLastRow
    sText = "'"
    For i = 1 To 3
    sText = sText & .Cells(lRow, iCol + i)
    Next
    .Cells(lRow, iCol) = sText
    Next
    .Cells(1, iCol) = "Merge"
    For i = 1 To 3
    .Columns(iCol + 1).EntireColumn.Delete
    Next
    .Columns(iCol).AutoFilter Field:=1, Criteria1:="="
    .Rows("2:" & lLastRow).Delete
    .AutoFilterMode = False
    End With
    Set wks = Nothing
    End Sub</pre>



    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge columns/hide rows (Excel 2007)

    Thank you so much Steve. I will talk to the operations/mainframes staff and see if this will work for them. We really appreciate your help!

    Melanie

Posting Permissions

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