Combining 2 tables into 1 and use Table2's column widths (hoping for workaround dealing merged cells

macropod · #16 09-26-2015, 04:21 AM

Quote:

Originally Posted by CodingGuruInTraining

I'm experiencing some minor issues when I run this code:
- The tables as a whole are left aligned, then some are halfway off the left side. How could you make them centered in the page?

When I ran it, the macro centred the tables on the page.

Quote:

- When I try to add code to repeat the first row, I get an error again about merged cells. Is it a matter of where it is placed in the code or how? I've been trying this code:

Code:

Tbl.Rows(1).HeadingFormat = True

You can't do that in a table with vertically-merged cells. The simplest way around that is to select a cell on that row and use the selection to apply the heading format.

Quote:

- The table gets messed up when it comes across unmerged cells in column 1 (for obvious reasons), which can happen often with these documents. Could there be a check added to see if the cells are merged first and to merge them if they are not? I am thinking something like this (I know this isn't correct, but I'm still learning):

Code:

If Case 1: .Cells.Merge = False Then
            .Cells.Merge
            Else Next

What you need to do here is to test whether the 5th cell in the group is in column 1; it shouldn't be. If it is, use the selection process again to merge the column1 cells

Quote:

- I'd like the horizontally merged cells to be a set height, but still be able to expand if too much content is added (AutoFit I think). My first attempt was with the following code, but it didn't seem to do anything.

Code:

If .Cells().Width = InchesToPoints(6.21) Then
                    .Cells.Height = InchesToPoints(0.8)
                End If

The way to do that is via the SetHeight method.

I've incorporated the above, plus comments into the code in my previous post.

CodingGuruInTraining · #17 09-27-2015, 12:09 AM

I'm getting an error with this section saying "The requested member of the collection does not exist." When I hover over the i, it says i = 10, if that helps at all.

Code:

If .Cells(i + 4).ColumnIndex = 1 Then
        Set Rng = .Cells(i).Range
        Rng.End = .Cells(i + 4).Range.End
        Rng.Select: Selection.Cells.Merge
End If

I found out why some tables were going off the page; the tables had text wrapping on, which I know can also mess with repeating header rows. I tried adding the code below (after a few attempts) to the With Tbl section, and it centered the table, but when I go to the table properties the text wrapping is still on, which makes the header row not repeat (visually). Oddly enough, one table that had text wrapping on had all the text centered after the macro ran. When I turned text wrapping ON on a different table (just for testing) and ran the macro, neither had centered text.

Code:

    With Tbl
     .Range.Rows.WrapAroundText = False

Sorry I keep forgetting little additions, but I was wondering how you set the vertical and horizontal alignments per column/row. In your previous code I was able to add a part to a Case (as shown below), but I don't see where I can fit this in your current code for specific sections.

Code:

        For i = 5 To .Cells.Count
          Select Case (i Mod 5) + 1
            Case 1: sWdth = InchesToPoints(0.45)
                .Cells().VerticalAlignment = wdCellAlignVerticalTop

I played around with an If/Then situation, but I couldn't get it to work. The bit below runs, but it doesn't do anything and I'd prefer not to go cell by cell.

Code:

      With .Range
        .Cells(1).Select: Selection.Rows.HeadingFormat = True
        For i = 1 To 4
          .Cells(i).Width = InchesToPoints(sWdth(i))
        Next
        ''my addition
        If .Cells.Width = InchesToPoints(0.45) Then
            .Cells().VerticalAlignment = wdCellAlignVerticalTop
        End If

The attached image shows the alignments I'm hoping for. We're so close!

macropod · #18 09-27-2015, 12:30 AM

Quote:

Originally Posted by CodingGuruInTraining

I'm getting an error with this section saying "The requested member of the collection does not exist." When I hover over the i, it says i = 10, if that helps at all.

In that case, I suggest you check the table that's causing the error - most likely its layout doesn't conform to what you've said they should.

Quote:

I found out why some tables were going off the page; the tables had text wrapping on, which I know can also mess with repeating header rows.

The code unwraps all the tables in the sample you provided and centres them on the page, so I'd expect it to do likewise with your other tables.

Quote:

Sorry I keep forgetting little additions, but I was wondering how you set the vertical and horizontal alignments per column/row.

You'd have to do that on a cell-by-cell basis, for which you could, for example, set one format for the whole table, then modify just the cells that differ from what the table as a whole should have. You really shouldn't be trying to work with the cell widths but with their indexing; given that the code processes all except the first row in blocks of 5 cells, it's a trivial undertaking to work within that structure. In any event, you should be looking at the question of what paragraph Styles ought to apply to the cells and using those, not just trying to override whatever formatting is already there.

CodingGuruInTraining · #19 09-27-2015, 12:01 PM

Today the same section is giving me a different error, this time regarding the Selection part:

Code:

          If ((i Mod 5) + 1) = 1 Then
            If .Cells(i + 4).ColumnIndex = 1 Then
              Set Rng = .Cells(i).Range
              Rng.End = .Cells(i + 4).Range.End
              Rng.Select: Selection.Cells.Merge
            End If

I'm using the same document I provided, see attached image. The unmerged sample is at the bottom in the last table.

I got rid of text wrapping by adding the following code near the end, but that seemed to make the columns slightly offset from the first row. I tried fixing this by adjusting the Array widths slightly, some to 1000s of an inch, but that only fixed some. Any idea why this would be?

Code:

      End With
      Tbl.Rows.WrapAroundText = False    
      Tbl.Rows.Alignment = wdAlignRowCenter  
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

macropod · #20 09-27-2015, 02:51 PM

The macro runs fine for me with the document you attached - all tables end up centred on the page with uniform layouts and no errors. I am unable to reproduce the problem you say you're having.

CodingGuruInTraining · #21 09-27-2015, 05:39 PM

I understand and I appreciate all of your efforts thus far. Perhaps you could try explaining what this code below is actually doing. To start with, how does the program know the criteria you listed?

Code:

'Format the remaining cells, in groups of 5.
        'The first cell in each group spans two rows and
        'the first row in each group contains 4 cells but
        'the second row contains 1 cell spanning the width
        'of the last 3 cells of the first row
        For i = 5 To .Cells.Count
          'If it's the first cell in a group, ensure it's both merged and centred vertically
          If ((i Mod 5) + 1) = 1 Then
            If .Cells(i + 4).ColumnIndex = 1 Then
              Set Rng = .Cells(i).Range
              Rng.End = .Cells(i + 4).Range.End
              Rng.Select: Selection.Cells.Merge
            End If

CodingGuruInTraining · #22 09-28-2015, 10:30 PM

I figured out what the problem was with the merging code; there were some extra tables in my document that follow a different cell arrangement and the macro didn't like them. When I deleted those tables, the code was able to run all the way through! I have been doing a lot of testing and I believe the issue with offset columns is due to either specific rows and/or the tables (corrupted?). I am still working it out to be sure.

Is there a way to present a pop-up or something where you can designate a page range for the macro to only run on?

macropod · #23 09-29-2015, 02:07 PM

To work with just the selected tables, try changing:
wdDoc As Document
to:
RngSel As Range
and changing:
Set wdDoc = ActiveDocument
With wdDoc
to:
Set RngSel = Selection.Range
With RngSel

CodingGuruInTraining · #24 10-07-2015, 07:43 PM

That last addition will help. There are still some tables that don't work exactly right, which is a problem with the documents not the macro. How would you finish the original macro so that it moves the data from the original table into the new one while using the selected range option? I think I got the right code below and tried incorporating the selection part, but it's not working.

Code:

Sub Demo()
Dim wdDoc As Document, Tbl As Table, Rng As Range, i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Set RngSel = Selection.Range
With RngSel
  For i = .Tables.Count To 1 Step -1
    With .Tables(i).Range
      j = (.Cells(.Cells.Count).RowIndex - 1) / 2
      .Paragraphs.Last.Next.Range.InsertBefore vbCr & vbCr & vbCr & vbCr
      Set Rng = .Paragraphs.Last.Next.Next.Range
      Rng.Collapse wdCollapseStart
      Set Tbl = wdDoc.Tables.Add(Range:=Rng, NumRows:=2, NumColumns:=4, AutoFitBehavior:=False)
      With Tbl
        .Borders.Enable = True
        .Rows.HeightRule = wdRowHeightAuto 
        .Rows(1).Height = InchesToPoints(0.25)
        .Rows(2).Height = InchesToPoints(0.5)
        .Cell(2, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Columns.PreferredWidthType = wdPreferredWidthPoints
        .Rows(1).Shading.BackgroundPatternColorIndex = wdTurquoise
        .Columns(1).Shading.BackgroundPatternColorIndex = wdTurquoise
        .Columns(1).Width = InchesToPoints(0.45)
        .Columns(2).Width = InchesToPoints(1.5)
        .Columns(3).Width = InchesToPoints(2.38)
        .Columns(4).Width = InchesToPoints(2.33)
        Set Rng = .Range
        With Rng
          .SetRange Start:=.Cells(6).Range.Start, End:=.Cells(8).Range.End
          .Cells.Split NumRows:=2, NumColumns:=1, MergeBeforeSplit:=False
          .Rows.HeightRule = wdRowHeightAuto
          .SetRange Start:=Tbl.Range.Cells(9).Range.Start, End:=Tbl.Range.End
          .Cells.Merge
          .SetRange Start:=Tbl.Range.Cells(5).Range.Start, End:=Tbl.Range.End
          .Copy
        End With
        For k = 2 To j
          With .Range.Paragraphs.Last.Next.Range
            .InsertBefore vbCr
            .Paste
          End With
        Next
      End With
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

macropod · #25 10-07-2015, 07:48 PM

The code in post #14 doesn't create a duplicate table or move any data - it simply reformats the existing tables.

Although you're now proposing to use code based on that from post #8 for the malformed tables, there'd be no sensible way to create a new ones based on them and transfer the data - one simply couldn't guarantee the outcome would be anything like what is needed. Best to fix the problem tables before running the macro from post #14 against them.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Table will not allow sorting because "cells are merged". I can't find the merged cells.	wendyloooo	Word Tables	1	05-26-2015 01:19 PM
Splitting one column into two with two different widths	officeboy09	Excel	6	12-15-2013 09:48 PM
Pasting tables from Excel 2010 into Word 2010 - How to fix column widths?	GracieB	Word	7	10-02-2013 06:24 AM
Adjusting column widths	norwood	Word VBA	0	09-24-2013 06:53 AM
VBA code to fix column widths	molesy	Excel	3	09-16-2013 12:51 PM