1
votes

I have some VBA code that works really well at deleting any columns in a single worksheet where any columns are headed with a 'FALSE' value. However, when I repeat the code within the same sub to take effect on a different worksheet, I get a Compile Error alert (Duplicate Declaration in Current Scope). Can anyone advise how I could make the extract below work across multiple worksheets (i.e. both "Outcome Summary" sheet and "Method Statement Evaluation" sheets plus other sheets)? Also, please note that each sheet has a different number of columns within the lColumn range.

Sub DeleteColumns()

With Sheets("Outcome Summary")
.Select
Dim lColumn As Long
Dim iCntr As Long
lColumn = 108
For iCntr = lColumn To 1 Step -1
If Cells(1, iCntr) = False Then
Columns(iCntr).Delete
End If
Next

With Sheets("Method Statement Evaluation")
.Select
Dim lColumn As Long
Dim iCntr As Long
lColumn = 156
For iCntr = lColumn To 1 Step -1
If Cells(1, iCntr) = False Then
Columns(iCntr).Delete
End If
Next
End With

End Sub

enter image description here

2
You have the same variable declared twice. You cannot do that. Remove the second one for each of them. - braX

2 Answers

1
votes

And shorten your code by using a loop.

This incorporates BigBen's point about finding the last used column (based on row 1) in case it differs across sheets.

Sub DeleteColumns()

Dim ws As Worksheet

For Each ws In Worksheets
    Select Case ws.Name
        Case "Outcome Summary", "Method Statement Evaluation" 'affected sheets
            Dim lColumn As Long
            Dim iCntr As Long
            lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
            For iCntr = lColumn To 1 Step -1
                If ws.Cells(1, iCntr) = False Then
                    ws.Columns(iCntr).Delete
                End If
            Next
    End Select
Next ws

End Sub
0
votes

Thanks @SLR and @BigBen. In order to further expand this so that I can also delete identified rows (in addition to identified columns) I have tweaked your example as follows and all is working well:

Sub DeleteRowsAndColumns()

Dim ws As Worksheet

For Each ws In Worksheets
    Select Case ws.Name
        Case "Outcome Summary", "Method Statement Evaluation" 'affected sheets
            Dim lColumn As Long
            Dim lRow As Long
            Dim iCntr As Long

            'This section deletes columns
            lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
            For iCntr = lColumn To 1 Step -1
                If ws.Cells(1, iCntr) = False Then
                    ws.Columns(iCntr).Delete
                End If
            Next
            'this section deletes rows
            lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
            For iCntr = lRow To 1 Step -1
                If ws.Cells(iCntr, 1) = False Then
                    ws.Rows(iCntr).Delete
                End If
            Next
    End Select
Next ws

End Sub

Thanks once again.