VBA: Selecting specific rows across multiple tables

[bartonfink83]

Hello,


The macro below successfully highlights a specific range of rows from one table. However, I have about 600+ tables to edit. Therefore, I need to find a way to modify this macro somehow, so that it highlights the same range of rows across all tables. I've been experimenting all day, slapping on bits of code here and there, but to no avail. Any help/suggestions would be greatly, greatly appreciated. Many thanks, and Godspeed...

Sub cellSel()
Dim myCells As Range
With ActiveDocument
Set myCells = .Range(Start:=.Tables(1).Cell(4, 1).Range.Start, _
End:=.Tables(1).Cell(15, 12).Range.End)
myCells.Select
End With
End Sub

[SamT]

Dim Tbl As Object
Dim myCells As Range

For Each Tbl in ActiveDocument.Tables
Set myCells = .Range(Start:=Tbl.Cell(4, 1).Range.Start, _
End:=Tbl.Cell(15, 12).Range.End)
myCells.Format = ???
Next Tbl
End Sub

[bartonfink83]

Hi, SamT,


Thank you for your expertise and your speedy reply; I appreciate it.

Having just re-run the macro with the newly-incorporated script, a Compile Error window comes up ("Invalid or unqualified reference"), highlighting the first ". Range" in VBA Editor. As you can probably tell, my grasp of VBA is deplorable, and, thus, I have no idea why this error is coming up. Any suggestions? Many thanks once again for taking the time to help!

[gmayor]

The macro needs to be modified as follows in order to work. The start and end cells must exist in all the cells or it will fail as it stands.

Sub Macro1()
Dim Tbl As Table
Dim myCells As Range
For Each Tbl In ActiveDocument.Tables
Set myCells = Tbl.Range
myCells.start = Tbl.Rows(3).Cells(1).Range.start
myCells.End = Tbl.Rows(5).Cells(3).Range.End
'myCells.HighlightColorIndex = wdTurquoise 'highlights the text content
myCells.Shading.BackgroundPatternColor = wdColorTurquoise 'colours the cell background
Next Tbl
lbl_Exit:
Set Tbl = Nothing
Set myCells = Nothing
Exit Sub
End Sub

[bartonfink83]

Hi, gmayor,


Thank you for taking the time to respond and help. I tried your macro, but the problem lies with merged cells contained within the tables I'm looking to edit, which I foolishly neglected to mention. Consequently, I'm being met with a 5991 runtime error. Had it not been for the merged cells, I reckon this macro would have worked like a freaking charm. Darn it!

16148

I have a ridiculous amount of tables to clean up (mostly output from SPSS), and I just need to remove a range of rows, similar to what's pictured above, in every table. The other problem, as well as the merged cells, is the fact that there is variation, with respect to the number of columns, in each table. The number of rows -- that incorporate merged cells -- however, is fixed.

Seriously, though, I really appreciate the help that's been provided! If anybody could shed any more light on this macro conundrum, that would be, like, noice. Many thanks.

[gmayor]

Merged cells are an absolute pain to program in Word VBA as Word does not provide any indication whether the cells are merged or not. What EXACTLY do you want to do with these tables? Whether it is possible using VBA is another matter entirely.

[SamT]

:dunno

Copy the part to keep? Delete the entire table? Paste the copy back in?

:dunno

[bartonfink83]

Hi, gmayor and SamT,


Once again, thank you for your replies, and also for reaching out to help; I'm very grateful. I very much realize I've not been clear in terms of what exactly I'm trying to do here -- I apologize.

OK -- I have a LOT of tables to clean (mostly output from SPSS). I want to keep two-thirds of every table, but the first third of each table (not including the header row) I would like to delete. The number of rows that need to be deleted from each of the tables is exactly the same throughout, but the number of columns from table to table differ, and, within the sections that need to be deleted are merged cells. The example table, above, represents the one-third of each whole table that needs to be removed from each table (albeit with the actual data suppressed, obviously). If I can, in any way, automate the deletion process, it would be wonderful. But, I wholly realize that the process of writing a macro to deal with these table nuances is far from easy. Either way, I appreciate you taking the time to respond and help.

[gmayor]

Whether what you ask is possible is still unclear. You say you want to remove 'a third' of the tables. Which 'third'. The top third? The top left 'third'? The top right 'third'?
If we can assume the top third, leaving the header row, and the table format is always the same (or at least the first four columns are always merged the same way) and there are no vertically merged cells in the rest of the table, the following macro may work, but without seeing the tables or knowing how they differ from one another, it is a leap in the dark. The macro works by splitting the merged cells and then deleting the rows, but that won't work if there are merged cells in the part of the table you want to keep.

My overall feeling is that this is a task too far for VBA


Sub Macro1()
Dim oTable As Table
Dim iRow As Long
Dim iCell As Long
For Each oTable In ActiveDocument.Tables
If oTable.Rows.Count > 13 Then
iCell = oTable.Columns.Count
oTable.Range.Cells(iCell + 1).Split 12, 1
oTable.Range.Cells(iCell + 2).Split 8, 1
oTable.Range.Cells(iCell + 3).Split 4, 1
oTable.Range.Cells((iCell * 5) + 3).Split 4, 1
oTable.Range.Cells((iCell * 9) + 2).Split 4, 2
For iRow = 13 To 2 Step -1
oTable.Rows(iRow).Delete
Next iRow
End If
Next oTable
End Sub