Forum Discussion

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Feb 22, 2023

NikolinoDE 

Perhaps I’ve not able to explain the matter properly.

I have several columns in a sheet

I managed to deleted the columns other than SGST, CGST & IGST thru VBA.

only columns remain are SGST, CGST & IGST having different tax categories (e.g. 5%, 18% etc)

now need to total of SGST row-wise, CGST row-wise & IGST row-wise

in separate column inserted automatically after the last column of each category.

Plz see the sheets attached.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 22, 2023

    Vimal_Gaur 

    Here is some VBA code:

    Sub InsertTotals()
    Const HeaderRow = 2 ' row number of the headers
    Dim LastRow As Long
    Dim StartCol As Long
    Dim Col As Long
    Dim OldCat As String
    Dim NewCat As String
    Dim FillColor As Long
    LastRow = Cells(HeaderRow, 1).End(xlDown).Row
    Application.ScreenUpdating = False
    Col = 3
    StartCol = 3
    OldCat = Left(Cells(HeaderRow, Col).Value, 4)
    FillColor = Cells(HeaderRow, Col).Interior.Color
    Do
        Col = Col + 1
        NewCat = Left(Cells(HeaderRow, Col).Value, 4)
        If NewCat <> OldCat Then
            Range(Cells(HeaderRow, Col), Cells(LastRow, Col)).Insert Shift:=xlShiftToRight
            Cells(HeaderRow, Col).Value = "Total " & OldCat
            Range(Cells(HeaderRow + 1, Col), Cells(LastRow, Col)).FormulaR1C1 = _
                "=SUM(RC[" & StartCol - Col & "]:RC[-1])"
            With Range(Cells(HeaderRow, Col), Cells(LastRow, Col))
                .Borders.LineStyle = xlContinuous
                .Interior.Color = FillColor - RGB(20, 20, 20)
            End With
            Col = Col + 1
            StartCol = Col
            OldCat = NewCat
            FillColor = Cells(HeaderRow, Col).Interior.Color
        End If
    Loop Until NewCat = ""
    Application.ScreenUpdating = True
End Sub

Resources