Originally Posted by
dazzys
I generally don't list the cost of each item, I simply provide a total cost per Area and then a Total project cost at the end of the document. Is it somehow possible to hide / omit the Total Cost column in the Word document and have a total per Area?
Try:
Sub TblFmt()
Application.ScreenUpdating = False
Dim r As Long, StrDesc As String, StrTmp As String, SngVal As Single
With ActiveDocument
.Fields.Locked = False
.Fields.Update
With .Tables(1)
.Rows(1).Range.Font.Bold = True
.Rows(.Rows.Count).Range.Font.Bold = True
.Split .Rows.Count - 2
.Sort True, 1, wdSortFieldAlphanumeric
.Range.Characters.Last.Next.Delete
StrDesc = .Cell(.Rows.Count - 2, 1).Range.Text
For r = .Rows.Count - 2 To 2 Step -1
If .Cell(r, 1).Range.Text <> StrDesc Then
.Rows.Add .Rows(r + 1)
StrDesc = .Cell(r, 1).Range.Text
With .Cell(r + 1, 2).Range
.Text = Split(StrDesc, vbCr)(0) & " Estimate"
.Font.Italic = True
.ParagraphFormat.Alignment = wdAlignParagraphRight
End With
.Cell(r + 1, 1).Range.Text = Split(StrDesc, vbCr)(0)
End If
Next
SngVal = CSng(Replace(Split(.Cell(2, 4).Range.Text, vbCr)(0), "£", ""))
.Cell(2, 4).Range.Text = vbNullString
For r = 3 To .Rows.Count - 2
If .Cell(r, 1).Range.Text = StrDesc Then
StrTmp = "0" & Replace(Split(.Cell(r, 4).Range.Text, vbCr)(0), "£", "")
SngVal = SngVal + CSng(StrTmp)
.Cell(r, 1).Range.Text = vbNullString: .Cell(r, 4).Range.Text = vbNullString
Else
.Rows(r).Borders(wdBorderTop).LineStyle = Options.DefaultBorderLineStyle
StrDesc = .Cell(r, 1).Range.Text
.Cell(r - 1, 4).Range.Text = Format(SngVal, "£#,##0.00")
StrTmp = "0" & Replace(Split(.Cell(r, 4).Range.Text, vbCr)(0), "£", "")
SngVal = CSng(StrTmp): .Cell(r, 4).Range.Text = vbNullString
End If
Next
For r = 2 To .Rows.Count
.Cell(r, 3).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Cell(r, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphRight
Next
End With
.Fields.Locked = True
End With
Application.ScreenUpdating = True
End Sub
Bookmarks