Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    VB to insert space, create sub headings, and merge cells

    Hi All,



    Not sure if this is possible in VB and have been searching all day via Google but to no avail.



    On the attached spread sheet I have some data in the before sheet that I want to make look like the After sheet. The problem is that the ranges and section may change.



    I want to split the sheet into subsections by deal type (column A) and then ensure that each sub section has column headings and then above the column headings have the deal type merged along the cells.



    1. I would like to insert 3 blank rows where column A changes.

    2. I would then like to copy the headings from A3:H3 into each type section

    3. I would like to copy the type, i.e. 1 Ė Yes, into column B and then merge B:H and repeat for each instance.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi

    see attached file.
    click button [Process data] on sheet [Before]

    zeddy
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    stimpsond1 (2013-03-18)

  4. #3
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Zeddy, this is fantastic, exactly what I was after.

    The code is also so simple I can follow it as well.

    Thanks very much

    Dean

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dang Zeddy you did it again!

    Just for laughs here's slightly different code to accomplish the same task.
    Code:
    Option Explicit
    
    Sub MyReformat()
    
       Dim lCurRow As Long
       Dim lMaxRow As Long
    
       Application.ScreenUpdating = False
       lMaxRow = Rows().Count
       [a4].Select
       
       Do While True      'Infinate loop exited by test on row number!
       
         With Selection
         
             With Range(.Offset(-2, 1).Address(, , xlA1) & ":" & .Offset(-2, 7).Address(, , xlA1))
                 .HorizontalAlignment = xlCenter
                 .MergeCells = True
                 .Value = Selection.Value
                 With .Interior
                     .Pattern = xlSolid
                     .PatternColorIndex = xlAutomatic
                     .Color = 255       'Red
                 End With    'Selection.Interior
                 
             End With        'Range
             
             .Offset(-1, 1).Value = "Name"
             .Offset(-1, 2).Value = "Place"
             .Offset(-1, 3).Value = "Server"
             .Offset(-1, 4).Value = "Deal"
             .Offset(-1, 5).Value = "Number"
             .Offset(-1, 6).Value = "Region"
             .Offset(-1, 7).Value = "Amount"
             
             With Range(.Offset(-1, 1).Address(, , xlA1) & ":" & .Offset(-1, 7).Address(, , xlA1))
                 With .Interior
                     .Pattern = xlSolid
                     .PatternColorIndex = xlAutomatic
                     .Color = 65535     'Yellow
                 End With    'Selection.Interior
                 
             End With        'Range
             
         End With            'Selection
         
         On Error Resume Next
         Selection.End(xlDown).Select
         On Error GoTo 0
         lCurRow = Selection.Row
         If lMaxRow = lCurRow Then Exit Do
         Rows(Format(lCurRow) & ":" & Format(lCurRow + 2)).EntireRow.Insert
         Selection.End(xlDown).Select
         
       Loop
       
       [A1].Select
       
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    stimpsond1 (2013-03-19)

  7. #5
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Quick question please, the For I = 1 -9 I donít actually get, I assume it is implying to loop 8 times but not totally sure.

    If the deal type is a variable, i.e. it changes each day sometimes 3, sometimes 10 is there a way to factor that in?

    Thanks

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Dean

    I was being lazy. Your sample had 9 Deal types in column [A], so I assumed there would always 9 deal types but with different number of data rows per deal.
    So the loop was only required to run 8 times, since the first deal type was already done in the beginning section of the routine.

    NOTE: RG's solution works for any number of deal types.

    So I have factored in the number of deal types in my second version, as attached.
    Please try this version. You can delete some deal types, or add more if required.
    It also works if there is only ONE deal type in column [A]

    I have also documented the vba code, made it go faster (by eliminating screen refresh during processing), and simplified it by removing stuff not actually required.
    The idea is to show you how to do stuff as well as give a solution.

    RG: It's great to have alternative solutions!

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    stimpsond1 (2013-03-19)

  10. #7
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Zeddy, it worked a treat, thank you so much. Retired Geek, thanks for you responses too.

    It's amazing how quick some people respond and how helpful they are. Really do appreciate this and i'm learning as i go along but with help and detail like you put on the sheet i can adopt this for others stuff i need to learn. Thanks again.

Posting Permissions

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