Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts

    Merged Cells causing issues

    Hi everyone!

    I tried really hard to fix this one myself I promise....

    Basically I am trying to merge two reports ( they are set, I cannot change them unfortunately... I've tried but it won't happen as it's external software ) that I can run as both of them have valuable information in on about 130 different agents.

    The problem I have that one of the reports has a wierd format - based on the number of different "break codes" that have been entered each line has a different number of rows assigned to it. So if only two unique codes have been entered, the unique number ( the part I need to pull the information out with vlookups and counts and all that jazz ) is put into two cells that have been merged.
    I'm really bad at explaining this so I've attached a workbook so what I'm trying to say is a bit clearer...

    Basically I need to pull out all information that relates to each number in Column A, but as they could have a number of different rows assigned to it cell references will not work. A vlookup doesn't work properly because for some reason it only counts the individual cell that the number is in and not the entire merged range.

    I didn't need to try scripting based on static references as I realised that wouldn't work straight away as they will always change.

    In an ideal world i'd like the break codes to be transposed and each agent to have one unique row, but I'm aware that might not be possible... I was actually just hoping someone knew a script to demerge the cells in Column A and as it unmerges them copys the reporting number across the cells that were merged before if that makes sense.

    I'd appreciate any input at all on this, I've been manually unmerging and copying the reporting number to fill the space which as i'm sure you can imagine is VERY tedious.

    Thanks for any help

    R
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Rathril,

    Not sure if this is what you are looking for but the following code will unmerge your cells and setup your data so a vlookup could be performed.

    HTH,
    Maud

    Before Code:
    Rathril1.png

    After Code:
    Rathril2.png

    Code:
    Sub UnmergeCells()
    Application.ScreenUpdating = False
    '----------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim StartRow As Long, LastRow As Long, EndBlock As Long
    StartRow = 9
    LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    '----------------------------------------------
    'UNMERGE SHEET
    Range("B9:K" & LastRow).UnMerge
    '----------------------------------------------
    'SET BLOCK AND COPY DATA
    Do While StartRow < LastRow
        EndBlock = Range("B" & StartRow).End(xlDown).Row - 2
        Range(Cells(StartRow, 2), Cells(StartRow, 4)).Select
        Selection.Copy
        Range(Cells(StartRow + 1, 2), Cells(EndBlock, 4)).Select
        ActiveSheet.Paste
        StartRow = EndBlock + 2
    Loop
    [b2].Select
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If that is the need then this should be a bit more efficient

    Option Explicit
    Sub UnmergeCellsSAS()
    Dim fr As Long
    Dim lr As Long
    Application.ScreenUpdating = 0
    ActiveSheet.UsedRange.Cells.UnMerge
    fr = 8
    Do Until Cells(fr, "b").Value = "Totals"
    fr = Range("b8").End(xlDown).Row
    'MsgBox fr
    lr = Cells(fr, "b").End(xlDown).Row
    'MsgBox lr
    Cells(fr, "b").Resize(lr - fr, 3).Value = Cells(fr, "b").Resize(, 3).Value
    fr = lr
    Loop
    'FixColumnsIfDesired ' NOT necessary if you only need to be able to lookup
    Application.ScreenUpdating = 1
    End Sub
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Maybe changing the line 6 in post #3 from:

    Code:
    ActiveSheet.UsedRange.Cells.UnMerge
    
                  To
    
    ActiveSheet.UsedRange.Offset(8, 0).Cells.UnMerge
    might make it even "a bit more efficient" by not unmerging the entire header.

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I stand corrected IF the header part was needed. As I understood (not well explained) OP just wanted to get info from the file and not to make it pretty.
    We should always be trying to improve our coding. Be nice.

Posting Permissions

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