How to specify a range of table cells

[YossiD]

Sorry if this is a dumb question, but I haven't managed to find the answer on my own.

I am trying to set margins of all cells in a table row using VBA. I recorded a macro that selects the entire row and then sets the call margins. Manually this works fine, but when I run the macro it only sets the margins in the first cell of the row. When I look at the code there's a line that says
With Selection.Cells (1). I can change the number so it will set the margins in a different cell, but I haven't figured out how to specify a range of cells. Is there a way to do that?

[bkgashok]

what do you mean by cell margins...

[fumei]

1. macro recording ONLY uses Selection (which can be an issue at times).

2. the following will adjust all cells in the row the Selection is in:Sub CellCrap()
With Selection.Cells(1)
.TopPadding = InchesToPoints(0.3)
.BottomPadding = InchesToPoints(0.3)
.LeftPadding = InchesToPoints(0.3)
.RightPadding = InchesToPoints(0.3)
.WordWrap = False
.FitText = False
End With
End SubMind you, margins of 0.3 are kind dumb looking, but I did this for example purposes. So I am not sure why this does not work for you.

a) what version are you using? Hint: this is always a good thing to mention.
b) post your actual code.

[GOGGS75]

Hi

I think I know what problem YossiD is having as I am trying to solve a similar problem.

The following line can specify a 'cell' within a table with ROWNO% and COLNO% as variables.

Selection.Tables(1).Cell(Row:=ROWNO%,Column:=COLNO%).Range


If a macro knows the cell at the top left and bottom right of the range of cells within a table is there anyway this can be set as one range using VBA ?

A long way round the problem is to set a loop that keeps changing the cell specified then changing the properties of the cell. I think there will be a way of setting the range so that the properties only need to be changed once.

Goggs75

[fumei]

Just to demonstrate tables and document ranges, take a look at the attached.

The table starts at document.range 7 and ends at 25.

The code behind "What Range" on the top toolbar is:
Sub WhatRange()
Dim r As Range
Set r = ActiveDocument.Range( _
Start:=7, End:=InputBox("A number between 7 to 26."))
r.Select
End Sub
Simple enough. Make a range object with specific start/end and select it so you can see what that range ends up as.

The code uses 7 as the start (the start of the table), and whatever you input.

Try 11. The first cell is selected.
12. The first and second cells.
13 the first second and third cells

now try 16.

Better yet...put the cursor in the second cell, row 2. Now click "Sel RangeStart" on the top toolbar. This returns the Range.Start of the selection.

It returns 17.

Now use "What Range" and use 17.


Ooooooooooooooooooooooooh, they do not match.

[GOGGS75]

Fumei,

The problem your method of referencing cells in a table leaves is how to specify the range if it is the two dimensional rather than one dimensional location of cells a macro is using.

i.e. Cell(Row:=x, Column:=y) apposed to Cells(n)


I could see that Start property converts it from being two dimension to one dimensional

Cell(Row:=x,Column:=y).Start = Cells(n)


Is the conversion to one dimensional the way around the problem when specifying a range using two cells that are two dimensional ?

[fumei]

Not following.

Say you have the very first cell. It is identified by BOTH:

tableObject.Cell(x,y) or TableObject.Cell(1,1)

AND

tableObject.Range.Cells(n) or TableObject.Range.Cells(1)

They are equal valid, and point to the same cell.

What you are calling two-dimensional is really parameters RowIndex and ColumnIndex.

TableObject.Cell(RowIndex,ColumnIndex)

Thus these values are specific to ONE cell. Not a range. You can not have a range of:

Cell(RowIndex_of_CellA, ColumnIndex_of_CellB

Word is NOT Excel!

Range in Word is strictly numeric (non-dimensional). It has a .Start and a .End. And that number is specifically non-dimensional - i.e. it is the character count from PointA to PointB.

So in my demo, a range object with a .Start of 12 and and .End of 14 covers Cell(1,2) - or Cells(2) - to Cell(1,3) - Cells(3).

But ONLY because the cells have no content.

If you made the same range object with the same .Start and .End (12 and 14), but you had "Blah" in Cell(1,2), then the range object would cover:

"Bl"

"Start property converts it "

The Start property does not do any converting whatsoever. There is no conversion taking place.

Cell(Row:=x,Column:=y).Start = Cells(n)

This is NOT a valid concept in Word VBA.

Cell(Row:=x,Column:=y) = Cells(n)

is valid. The two identifiers are identical and point to the same object/cell.

ActiveDocument.Tables(1).Cell(1, 2).Range.Start is valid. Well it is valid in that it returns a single number - the Long representing the .Start of the range.

Cells(n) also returns a Long, but it is not a range value. It is the index number of the Cells collection of the range object (of the table).

The issue of Start is precisely why you can not use VBA with tables that have merged cells. And precisely why Columns do NOT have a .Range property.

ActiveDocument.Tables(1).Columns(1).Range

will fail, as .Range is NOT a valid property of Columns.

ActiveDocument.Tables(1).Rows(1).Range

will not fail, as .Range IS a valid property of Rows.

You clearly did not play around with my demo enough. Try this.
Sub tryThis()
Dim r As Range
Set r = ActiveDocument.Range( _
Start:=ActiveDocument.Tables(1).Cell(1, 2).Range.Start, _
End:=ActiveDocument.Tables(1).Cell(2, 3).Range.End)
r.Select
End SubSay on the same 3 row x 4 column table.

cell(1,1) cell(1,2) cell(1,3) cell(1,4)
cell(2,1) cell(2,2) cell(2,3) cell(2,4)
cell(3,1) cell(3,2) cell(3,3) cell(3,4)

If you run the above you get (with the range selected as bold)

cell(1,1) cell(1,2) cell(1,3) cell(1,4)
cell(2,1) cell(2,2) cell(2,3) cell(2,4)
cell(3,1) cell(3,2) cell(3,3) cell(3,4)

Note that the cells in Column 1 (row2) are NOT selected. Not are the cells in column4 (row1). Yet, you could argue those cells are "between" - in the "range of" - the paramters Cell(1,2) and Cell(2,3).

Yes?

It gets worse.

Try it again as:

Sub tryThis()
Dim r As Range
Set r = ActiveDocument.Range( _
Start:=ActiveDocument.Tables(1).Cell(1, 2).Range.Start, _
End:=ActiveDocument.Tables(1).Cell(2, 1).Range.End)
r.Select
End SubHey presto! The cell(1,1) is included in the range! It is clearly BEFORE the .Start of Cell(1,2)...but it IS included.

Nope, sorry, but you can not what you want to do. You can not reliably make a range object using cell values.

Word is NOT Excel.

[GOGGS75]

Fumei

Thanks for all your help, I can understand what you are saying and this has helped. I still feel it is debatable whether a range in a table is 'non-dimensional' with VBA on WORD.

The four lines below show that the start and end point have two dimensional information available.

Selection.Information(wdStartofRangeRowNumber)
Selection.Information(wdStartofRangeColumnNumber)
Selection.Information(wdEndofRangeRowNumber)
Selection.Information(wdEndofRangeColumnNumber)

I think these lines are available because something similar existed prior to VBA on WORD.


Goggs75

[YossiD]

Thanks for all the replies.

I will play around with the code samples when I have a chance. In the meantime I revised my macro to loop through the cells I needed to modify and change them one by one. Brute force, but it works.

After re-reading my question and all the replies, I realize that there are really two questions here:

#1. How to specify a range of cells to modify (as I originally asked), and
#2. How to modify whichever cells happen to be selected (highlighted)

I think that #1 is addressed in the replies, but is there a way to accomplish #2? this would be particularly useful since for some reason F4 doesn't work for setting cell margins (at least not in Word 2003, which is what I'm using).

[gmaxey]

I have not studied in depth anything posted here previously so I can't agree or disagree with the comments posted so far.

Have a look a the following code. The first procedure will process all of the cells in the row containing the seletion and then process just the cell containing the selection.

The second procedure uses cell information in a rather klunky process to process a range of cells.

No serious testing done here so merged or split cells may cause problems.


Sub ScratchMaco()
Dim oRow As Row
Dim oCell As Cell
'Modify all cells in the row containing the selection
Set oRow = Selection.Tables(1).Rows(Selection.Cells(1).Range.Information(wdEndOfRangeR owNumber))
For Each oCell In oRow.Cells
With oCell
.LeftPadding = InchesToPoints(0.2)
.RightPadding = InchesToPoints(0.2)
End With
Next oCell
'Modify the cell containing the selection.
Set oCell = Selection.Cells(1)
With oCell
.LeftPadding = InchesToPoints(0.5)
.RightPadding = InchesToPoints(0.5)
End With
End Sub
Sub ScratchMacoII()
'Tested on a 5 X 5 table
Dim i As Long, j As Long, k As Long, l As Long
Dim oCell As Cell
'Process the range B2:D4
i = 2: j = 2: k = 4: l = 4
For Each oCell In Selection.Tables(1).Range.Cells
If oCell.Range.Information(wdEndOfRangeColumnNumber) >= i And oCell.Range.Information(wdEndOfRangeRowNumber) >= j And oCell.Range.Information(wdEndOfRangeColumnNumber) <= k And oCell.Range.Information(wdEndOfRangeRowNumber) <= l Then
MsgBox "Test"
With oCell
.LeftPadding = InchesToPoints(0.5)
.RightPadding = InchesToPoints(0.5)
End With
End If
Next oCell
End Sub

[fumei]

We have to be careful here. Selection is one thing. Using a Range object is quite another.

For one thing, you can select non-contiguous areas with Selection, and action them in the GUI. And ONLY in the GUI. If you make the same non-contiguous Selection and try to action it via code, it will not work.

You can NOT action non-contiguous areas with any range object. And you do NOT get valid VBA data - wdStartofRangeRowNumber) for example - if you have non-contiguous selection.

cell1,1 cell1,2 cell1,3
cell2,1 cell2,2 cell2,3
cell3,1 cell3,2 cell3,3
cell4,1 cell4,2 cell4,3

Cells 2,1 and 3,3 are selected. You can - say - apply Bold to them in the GUI...and it works.

You can NOT do it via VBA. AND, if you execute:
Sub SelectionData()
MsgBox _
"Start of Range Row: " & _
Selection.Information(wdStartOfRangeRowNumber) & vbCrLf & _
"Start of Range Column: " & _
Selection.Information(wdStartOfRangeColumnNumber) & vbCrLf & _
"End of Range Row: " & _
Selection.Information(wdEndOfRangeRowNumber) & vbCrLf & _
"End of Range Column: " & _
Selection.Information(wdEndOfRangeColumnNumber)
End Sub
you get:

Start of Range Row: 3
Start of Range Column: 3
End of Range Row: 3
End of Range Column: 3

The first cell is completely ignored. There is no way around this. And, IMO, this is correct.

Yes, you could say Word tables cell are dimensional. However, what I was trying to convey was that it is a big mistake to think of them as having anything remotely like the dimensionality of Excell cells. They do not.

I will try again with a similar demo. It has the table above. The procedure is on the top toolbar as "Selection Data".

Select the table like this:

cell1,1 cell1,2 cell1,3
cell2,1 cell2,2 cell2,3
cell3,1 cell3,2 cell3,3
cell4,1 cell4,2 cell4,3

Run it. You get:

Start of Range Row: 2
Start of Range Column: 2
End of Range Row: 3
End of Range Column: 3

Fine. So tell me.....if you did not know (and can look and see) does that mean Cell(3,1) is included....or not?

Answer: no, you can NOT tell. It may be, or it may not be. The point being is that if it IS selected and if it is is NOT selected...you get the same answer.

Start of Range Row: 2
Start of Range Column: 2
End of Range Row: 3
End of Range Column: 3

So, if a given cell is included, or not, returns the same answer as to the range "parameters"...ummmmm, exactly how reliable is that? Hmmmm?

And...VBA will not even run if there are merged cells. And for - again - precisely these reasons.

[fumei]

PS. re: dimensionality

While wdStartOfRangeRowNumber and wdStartOfRangeColumnNumber appear to be dimensional, they are not really. They are the index values of the ROW and COULMN objects the range is in, not any value of the range object itself.

Range is strictly linear. It has no X,Y values.