Dynamic Merging of cells

Hi everyone,

I have a challenge that I'd like some help with:

I have a report right now that looks something like the following

Code:

---

SALES_NUM  CUSTOMER  ITEMS SOME_ORDER_INFO COMMENTS
12345678    Buyer1  Item A  ~~~~~~~~~~~~ BIG COMMENT
12345678            Item B
12345678            Item C
12345678            Item D
12345678            Item E

44444444  Buyer2  Item A  ~~~~~~~~~~~~~  COMMENT

---

For the purposes of this report, the users don't want to see repeating information - except for the sales numbers.

The problem is that "BIG COMMENT" is up to 2000 characters in length. Right now it's word wrapped and enlarges the first row of every order ... but this isn't ideal. If I could, I'd like to merge the cells on the right, so that I don't waste paper enlarging the first row, and then subsequent rows are empty.

New report:

Code:

---

SALES_NUM  CUSTOMER  ITEMS SOME_ORDER_INFO COMMENTS
12345678    Buyer1  Item A  ~~~~~~~~~~~~ BIG COMMENT
12345678            Item B                      IN
12345678            Item C                      ALL
12345678            Item D                      THESE
12345678            Item E                      CELLS

44444444  Buyer2  Item A  ~~~~~~~~~~~~~  COMMENT

---

Sort of idea.

The thing that might make this much easier, is the fact that there is an entirely empty (null) row between orders. The first row is guaranteed to have information if the order exists, and the first column is fully populated except for the empty fields between orders

Any ideas would be appreciated. :)

SB

I just tried this manually ...

Code:

---

  A        B        C        D        E            F            G            H            I
1  x        x        x        x        now is the time for all good men to come to
2
3
4
5

---

I then

1. selected E1:E5
2. clicked the Merge button .. "for all good" appears on row 5
3. clicked the Left Align button .. "now is the" appears on row 5
4. clicked Format menu option
   
   • clicked Cells
   • select Alignment tab
   • check Wrap text option in Text Control.

Now it looks like ...

Code:

---

  A        B        C        D        E            F            G            H            I
1  x        x        x        x        now is the
2                                      time for
3                                      all good
4                                      men to
5                                      come to

---

Now, is that all you were after?
Or do you want to create a macro to go through the
entire sheet and do the same thing?

Spoo

Hi Spoo, Thanks for the reply.

What I need is for a dynamic number of orders, with a dynamic number of rows per order, to have the final column merge text.

My solution was something like the following: (I'm at home right now actually)

Code:

---

Dim firstRowToMerge as Integer
firstRowToMerge = 2                  'this is because row 1 contains header information
For i = 2 .. maxrowcount
    If row is blank Then
        Range ("A" & firstRowToMerge & ":" & "A" &  (i - 1)).Merge
        firstRow = i + 1
    End If
Next i

---

The drawback to this is that it's extremely slow. However, with the uniqueness of the problem, this may be the closest I can get to an optimal solution.

Some paper saved at the cost of runtime :)

Edit: Here is the real deal:

Code:

---


Dim firstMerge as Integer
firstMerge = 2

For i = 3 To Range("A65536").End(xlUp).Row
         
      If Len(Range("A" & i).Value) <= 0 Then
                Range("AB" & firstMerge & ":" & "AB" & (i - 1)).Merge
                firstMerge = i + 1
      End If
Next i

---

SB

OK, so it is a macro situation.

You say it is "extremely" slow ...

1. what might maxrowcount be?
2. how long does it take?

I mainly work with VB6 as opposed to VBA, but there
might be a parallel.

There is something called a MSFlexGrid in VB6 that "mimics"
an Excel spreadsheet. It allows formatting individual cells ...

• font
• forecolor
• backcolor
• alignment

.. to name just a few. The point is that with a large FG, it
can be "painfully" slow to populate if the Visible property is
set to True while all of the formatting is being done.

However, if Visible is set to False while the loop processes
the formatting, and Visible is set to True after the loop is
complete, it is lightning fast.

I wonder if there is a comparable issue with Excel going on
here. I don't know enough here, off hand, to help, but it seems
like an Application-level Excel Property might be available.

EDIT:

I saw your Edit...

Code:

---

z1 = Range("A65536").End(xlUp).Row

---

.. a clever way to find the last used row. Nice ... :thumb:


Spoo

Changing column sizes for instance in Excel can be a very expensive operation time-wise ( a few seconds each column)

This has something to do with Excel recalculating page-breaks after each statement. Each page-break calculation becomes expensive because it calls a bunch of print driver procedures/functions. Supposedly we're able to disable this - but I haven't been able to get a significant performance boost doing so.

I think that may be the case here - as each item takes a few seconds, for a combined total of over 1 minute. The report sizes can vary .. from 100 rows to 1000ish.

This is in VB6, I do the formatting on a hidden excel window and then make it visible at the end.

Unfortunately for this application I opted out of MSFlexGrid - it had something to do with its data-bound properties not fitting my needs. Also, the user is set on having it in excel so she can print it :S

SB

Interesting info, especially the "it's in VB6" bit.

Would it be worth reconsidering going back to a MSFlexGrid?

If the source data resides in Access (or similar), maybe you could
first do a "quick dump" to an array. I do this alot.

Then, populate the FG from the array. This would eliminate the
data-bound issues you may have encountered. I've done merging with
FGs, but not in manner envisioned here .. still, it should be doable.

You could even write a sub to print from the FG.

From there, for your user's particular desire, as a "back-up", you could
populate Excel.

• Begin by clearing the sheet of all data, page-breaks, etc.
• Set col widths
• Populate the sheet
• Then invoke the page breaks.

A bit of work on your part, but it might speed things up, especially
if this is something that is done routinely.

Spoo

Sounds like you want something like the following:

Code:

---

Option Explicit

Sub prMergeOrders()
    Dim strCol          As String
    Dim strColM        As String
    Dim lRow            As Long
    Dim count          As Long
    Dim i              As Long 'Loop Counter
   
    i = 2 'Start Row // Row 1 has headers
    strCol = "A" 'Set to the column with the order numbers
    strColM = "B" 'Set to the column that you want to merge
   
    lRow = Range(strCol & "65536").End(xlUp).Row
   
    Do While i <= lRow
        count = WorksheetFunction.CountIf(Range(strCol & ":" & strCol), Cells(i, strCol).Value)
        Range(strColM & i).Resize(count, 1).Merge
       
        i = i + count
    Loop
End Sub

---

Tested on a small sample and it worked nicely for me. Not sure about runtimes for a larger sample.

@Spoo

I think the Application level property you're on about is this:

Code:

---

Application.ScreenUpdating

---

I normally do this at the top of each code module. Mainly this stops screen flicker but it also speeds up the macro a small amount.

Code:

---

With Application
    .ScreenUpdating = False
    .EnableEvents = False

'Add the line below to make it not calculate formula results (Not sure it's relevant here)
    .Calculation = xlCalculationManual
End With

---

Then do this at the end of the code:

Code:

---

With Application
    .ScreenUpdating = True
    .EnableEvents = True

'Add the line below to make it not calculate formula results (Not sure it's relevant here)
    .Calculation = xlCalculationAutomatic
End With

---