Hello all,
I'm trying to sum most of the blank rows in the attached file from column H to column T. I've used the following code to complete the task with a different file. With this code I only had to sum two tables. With the attached file I need to sum like 50 tables. So I wanted to see if anyone could help me with some code that would do this more efficiently than me creating 100's of last row variables to find the blank rows.
EDIT: UPDATED ATTACHMENT XLSM AND VBA CODE TO CURRENT SUGGUSTION![]()
LastRow1 = Range("A5").End(xlDown).Row LastRow2 = Range("A4").End(xlDown).End(xlDown).End(xlDown).Row LastRow3 = Range("A6").End(xlDown).End(xlDown).Row 'Sum and format table1 With Worksheets("Hours") .Range("H" & LastRow1 + 1 & ":T" & LastRow1 + 1).Formula = "=SUM(H4:H" & LastRow1 & ")" End With Range("H" & LastRow1 + 1 & ":T" & LastRow1 + 1).Select Selection.Font.Bold = True Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThick End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 'Sum and format table2 'On Error is here in case there isn't a second table On Error Resume Next With Worksheets("Hours") .Range("H" & LastRow2 + 1 & ":T" & LastRow2 + 1).Formula = "=SUM(H" & LastRow3 & " :H" & LastRow2 & ")" End With Range("H" & LastRow2 + 1 & ":T" & LastRow2 + 1).Select Selection.Font.Bold = True Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThick End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone On Error GoTo 0 End Sub
Bookmarks