The title may be similar to other questions but as far as I can tell no one has asked this one. I am trying to loop through all columns and add the sum of them at the bottom after the last row and make the values before the sum grey. Up until this point I have been writing out each column name but the sheets keep getting larger and this is becoming a waste of time because I know there must be a better way. Please keep in mind I am new to VBAs. The section of the code is:
LR = Range("I" & Rows.Count).End(xlUp).Row
Range("I1:I" & LR & "").Interior.Color = RGB(217, 217, 217)
Range("I" & LR + 2).Formula = "=SUM(I2:I" & LR & ")"
LR = Range("J" & Rows.Count).End(xlUp).Row
Range("J1:J" & LR & "").Interior.Color = RGB(217, 217, 217)
Range("J" & LR + 2).Formula = "=SUM(J2:J" & LR & ")"
LR = Range("K" & Rows.Count).End(xlUp).Row
Range("K1:K" & LR & "").Interior.Color = RGB(217, 217, 217)
Range("K" & LR + 2).Formula = "=SUM(K2:K" & LR & ")"
LR = Range("L" & Rows.Count).End(xlUp).Row
Range("L1:L" & LR & "").Interior.Color = RGB(217, 217, 217)
Range("L" & LR + 2).Formula = "=SUM(L2:L" & LR & ")"
I hope you can see a solution using a loop. Thank you.
No need to loop at all here for this:
Dim LR As Long
Dim sLastCol As String
' change this as needed
sLastCol = "L"
LR = Range("I" & Rows.Count).End(xlUp).Row
Range("I1", Cells(LR, sLastCol)).Interior.Color = RGB(217, 217, 217)
Range("I" & LR + 2, sLastCol & LR + 2).FormulaR1C1 = "=SUM(R2C:R[-2]C)"
or if you can determine the last column using the last populated cell in row 1, you could use:
Dim LR As Long
Dim lLastCol As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
lLastCol = Cells(1, Columns.Count).end(xltoleft).column
Range("I1", Cells(LR, lLastCol)).Interior.Color = RGB(217, 217, 217)
Range("I" & LR + 2, Cells(LR + 2, lLastCol)).FormulaR1C1 = "=SUM(R2C:R[-2]C)"
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments