Hi everyone,
I have a new job and it has a lot of tasks that involves doing the same thing in excel frequently so I am trying to learn VBA. I was able to get started with it but I got stuck with putting the rest of the subtotal on the rest of the column The file that I am working on has variable amount of rows and columns. Some of the columns has hours that needs to be subtotal at the bottom but some of the columns don't have data in all the rows but they are still needed to be subtotal until the last row. The code that I have so far were able to input the subtotal in the first column correctly since it has hours in all the rows but the rest of the column are getting subtotaled after the last row with data for that column which is not correct since it didn't include everyone else in the table after the last data that has zero/blank hours.
Here is the code that I have so far.
Sub Test1()
Dim lLR As Long
Dim lLC As Long
Dim i As Long
lLR = Cells(Rows.Count, 1).End(xlUp).Row
lLC = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
'Select Last column's letter
Columns("K").Select
Dim LastRow As Long
Dim OffsetRow As Long
Dim ColLetter As String
Dim LastCol As Long
'Formula will be 1 rows below the LastRow
OffsetRow = 1
'Get the selected column's letter
ColLetter = Split(Cells(1, Selection.Column).Address, "$")(1)
'Get the last row
LastRow = Range(ColLetter & Rows.Count).End(xlUp).Row
'Insert the formula 1 rows below the LastRow
Range(ColLetter & (LastRow + OffsetRow)).Formula = "=SUBTOTAL(9," & ColLetter & "2:" & ColLetter & LastRow & ")"
ActiveCell.Select
Selection.AutoFill Destination:=Range(ColLetter & LastRow : LastCol & LastRow), Type:=xlFillDefault
Application.ScreenUpdating = True
End Sub
Bookmarks