Format columns A-M only on the last row of a dynamic range

[Ralmd]

I have 5 different worksheets in a workbook that is created by a dumped from an estimating program as a report. On these 5 sheets I have totaled the columns I want as dynamic ranges since the data will shrink and expand based on the estimate. I am having trouble with the correct syntax to identify columns "A" thru "M" on the last row and put a Border as a total line and turn the font to bold.
I'm totaling the columns I need to by using the "Sums Field Labor Fringe Extended Total" below for each column. However. I can't identify the range to format columns A thru M only in the last row. the "Format Last Row Field Labor" is where I left off. The Screen shot.JPG attached shows the the "Field Labor" sheets with some values. Row 11 is the Total Row in this instance. All I need to do now is figure out how to put a top thick border and turn the Font to bold.


'Sums Field Labor Fringe Extended Total
Sheets("Field Labor").Select
Dim XtndTtl As Long
XtndTtl = Range("K" & Rows.Count).End(xlUp).Row
Range("K" & XtndTtl + 1).Formula = "=Sum(K2:K" & XtndTtl & ")"

'Format Last Row Field Labor Total
Sheets("Field Labor").Select
Dim lRow As Integer
Dim lColmn As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
lColmn = Range("A" & Columns.Count).End(xlToLeft).Column
Sheets("Field Labor").Range("A & lRow : lColmn").Borders(xlEdgeTop).Weight = xlThick

 

[Flaiban]

Try this code...

Sub Field_Labor()

Sheets("Field Labor").Select
Dim XtndTtl As Long
Dim totRow As Long
XtndTtl = 1
totRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(totRow, 4).Formula = "=SUM(D" & XtndTtl + 1 & ":D" & totRow - 1 & ")"
Cells(totRow, 6).Formula = "=SUM(F" & XtndTtl + 1 & ":F" & totRow - 1 & ")"
Cells(totRow, 8).Formula = "=SUM(H" & XtndTtl + 1 & ":H" & totRow - 1 & ")"
Cells(totRow, 10).Formula = "=SUM(J" & XtndTtl + 1 & ":J" & totRow - 1 & ")"
Cells(totRow, 11).Formula = "=SUM(K" & XtndTtl + 1 & ":K" & totRow - 1 & ")"


Dim c As Range, uRng As Range, x As Long


    For Each c In Range("D" & Rows.Count).End(xlUp)
        If c.Value <> "" Then
            If Not IsEmpty(c) Then
                x = c.Row
                    Set uRng = Application.Union(Cells(x, "D"), Cells(x, "F"), Cells(x, "H"), Cells(x, "J"), Cells(x, "K"))
                        uRng.Borders.LineStyle = xlContinuous
                        uRng.Borders.Weight = xlMedium
                        uRng.Font.Bold = True
                        uRng.HorizontalAlignment = xlCenter
            End If
        End If
    
    Next
End Sub

 

[kevin9999]

Replace this line:

Sheets("Field Labor").Range("A & lRow : lColmn").Borders(xlEdgeTop).Weight = xlThick

With this:

With Range(Cells(lRow, 1), Cells(lRow, 13))
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With
    .Font.Bold = True
End With

 

[Ralmd]

Thanks to both of you for your response.

Flaiban, You response put borders on all 4 sides of each cell in the total row that contained a value. I'm looking for a continuous border on the top of the last row (where the total is shown) from column A to M whether the cell has a value or not. I like the way you totaled the columns. I think it saves code and will probably run better. I will try totaling your way versus the way I have done it and see what happens. I very much appreciate your response.

Kevin9999,
Thanks for your response. Your response formats the font to bold but does not put the top border across cells A thru M. I think your response is closure to what I'm looking for if I could only get the border to show.
Below is what I changed to however the top border is showing up.

'Format Field Labor Total
Sheets("Field Labor").Select
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(lRow, 1), Cells(lRow, 13))
With .Borders(xlEdgeTop)
LineStyle = xlContinuous
.Weight = xlThick
End With
.Font.Bold = True
End With

 

[kevin9999]

Thanks to both of you for your response.

Flaiban, You response put borders on all 4 sides of each cell in the total row that contained a value. I'm looking for a continuous border on the top of the last row (where the total is shown) from column A to M whether the cell has a value or not. I like the way you totaled the columns. I think it saves code and will probably run better. I will try totaling your way versus the way I have done it and see what happens. I very much appreciate your response.

Kevin9999,
Thanks for your response. Your response formats the font to bold but does not put the top border across cells A thru M. I think your response is closure to what I'm looking for if I could only get the border to show.
Below is what I changed to however the top border is showing up.

'Format Field Labor Total
Sheets("Field Labor").Select
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(lRow, 1), Cells(lRow, 13))
With .Borders(xlEdgeTop)
LineStyle = xlContinuous
.Weight = xlThick
End With
.Font.Bold = True
End With

It looks like you're missing a dot before LineStyle - add it and try running the code again.

 

[Ralmd]

Still does not seem to work

'Format Field Labor Total
Sheets("Field Labor").Select
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(lRow, 1), Cells(lRow, 13))
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
End With
.Font.Bold = True
End With

 

[kevin9999]

Still does not seem to work

'Format Field Labor Total
Sheets("Field Labor").Select
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(lRow, 1), Cells(lRow, 13))
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
End With
.Font.Bold = True
End With

Unfortunately, I can't replicate the problem. It works when I run it.

 

[kevin9999]

See if this makes a difference

With Range(Cells(LRow, 1), Cells(LRow, 13))
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThick
    End With
    .Font.Bold = True
End With

 

[Ralmd]

I've tried in in a new with no other code and it runs fine. However It still will not run in the project workbook.

 

[kevin9999]

I've tried in in a new with no other code and it runs fine. However It still will not run in the project workbook.

What other code and/or conditional formatting do you have in the project workbook?