Pass Min and Max columns as parameters or user inputs? Also, the way it is
written, it will run on the active sheet, which can be selected prior to
running the code (to make it the active sheet) or the range references in the
subroutine will have to be fully qualified to identify specifically what
sheet it should run on (more on that at the end of the post). Also, I tested
it with both column numbers (1,2,3) and letters (A, B, C).
If as user inputs:
Sub test()
Dim rCol As Range
Dim MinCol As Variant
Dim MaxCol As Variant
Dim RngToDelete As Range
On Error Resume Next
MinCol = InputBox("Input Minimum Column")
MaxCol = InputBox("Input Maximum Column")
If IsNumeric(MinCol) Then
Set MinCol = Columns(CLng(MinCol))
Else: Set MinCol = Columns(MinCol)
End If
If IsNumeric(MaxCol) Then
Set MaxCol = Columns(CLng(MaxCol))
Else: Set MaxCol = Columns(MaxCol)
End If
If Not MinCol Is Nothing And _
Not MaxCol Is Nothing Then
For Each rCol In Range(MinCol.Cells(1, 1), _
MaxCol.Cells(Rows.Count, 1)).Columns
If Application.CountA(rCol) = 0 Then
If RngToDelete Is Nothing Then
Set RngToDelete = rCol
Else: Set RngToDelete = Union(RngToDelete, rCol)
End If
End If
Next rCol
End If
If Not RngToDelete Is Nothing Then _
RngToDelete.EntireColumn.Delete
End Sub
If parameters (not tested)
Sub test(MinCol As Variant, MaxCol As Variant)
Dim rCol As Range
Dim RngToDelete As Range
On Error Resume Next
'Delete these two lines
'MinCol = InputBox("Input Minimum Column")
'MaxCol = InputBox("Input Maximum Column")
Rest of code goes here......
If you need to qualify the worksheet then do it here - I used the codename
for the sheet (Sheet2), but you can also use the tab name with
Worksheets("YourSheetName") - I like using codename as it is less likely to
be changed, causing code to fail.
If IsNumeric(MinCol) Then
Set MinCol = Sheet2.Columns(CLng(MinCol))
Else: Set MinCol = Sheet2.Columns(MinCol)
End If
If IsNumeric(MaxCol) Then
Set MaxCol = Sheet2.Columns(CLng(MaxCol))
Else: Set MaxCol = Sheet2.Columns(MaxCol)
End If
