Tukukkk

The following code deletes Column J only:

If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells(xlCellTypeVisible)) _

        = Application.WorksheetFunction.Sum(Range("L:L").SpecialCells( _

        xlCellTypeVisible)) Then



    Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.



End If

However, I would like to delete both columns J and L at the same time. How can I do this?

You can easily modify your current code using Union().

Change this line:

Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.

to this

Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft

This will take both ranges and delete them simultaneously.

Your code block should now look as follows:

If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _

        xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _

        Range("L:L").SpecialCells(xlCellTypeVisible)) Then



    Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft



End If

I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:

With ThisWorkbook.Worksheets(1)



    If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _

            xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _

            .Range("L:L").SpecialCells(xlCellTypeVisible)) Then



        Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft



    End If



End With

Where you would replace the 1 in Worksheets(1) to either the:

• Correct worksheet index number, or


• The name of the worksheet, surrounded by double-quotes (ex. .Worksheets("Sheet1"))
  

You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:

Option Explicit

Public Sub test()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet2")

    With ws      'Your sheet name goes here

        If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then

            Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft

        End If

    End With

End Sub

The first argument passed to AGGREGATE is Function_num . 9 is SUM. The second argument is Options. 3 is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.

Edit:

If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.

Public Sub test()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        With ws

            If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then

                Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft

            End If

        End With

    Next

End Sub