VBA - Sub to combine four other subs

smitty120

New Member
Joined
Feb 18, 2014
Messages
9
Hello,
I’m fairly new to VBA, and this forum has already proved to be a fantastic resource. I have the following four subs, all of which are intended to hide columns B, D, F, and H if all cells but the header are empty. The subs are functional, but I would really like to be able to combine them into one sub. Everything else I’ve written towards this end either hides all or none of these columns and I’m a little stumped. Thank you!

Code:
Sub B()
Application.ScreenUpdating = False
For Each Cell In Range("B1:B1000").Offset(1)
If Cell.Value <> "" Then
    Exit Sub
End If
Next Cell
For Each Cell In Range("B1:B1000").Offset(1)
If Cell.Value = "" Then
    Columns("B").Hidden = True
End If
Next Cell
Application.ScreenUpdating = False
End Sub


Sub D()
Application.ScreenUpdating = False
For Each Cell In Range("D1:D1000").Offset(1)
If Cell.Value <> "" Then
    Exit Sub
End If
Next Cell
For Each Cell In Range("D1:D1000").Offset(1)
If Cell.Value = "" Then
    Columns("D").Hidden = True
End If
Next Cell
Application.ScreenUpdating = False
End Sub


Sub F()
Application.ScreenUpdating = False
For Each Cell In Range("F1:F1000").Offset(1)
If Cell.Value <> "" Then
    Output = MsgBox("Middle Name Found", vbOKOnly, "Sub D")
    Exit Sub
End If
Next Cell
For Each Cell In Range("F1:F1000").Offset(1)
If Cell.Value = "" Then
    Columns("F").Hidden = True
End If
Next Cell
Application.ScreenUpdating = False
End Sub


Sub H()
Application.ScreenUpdating = False
For Each Cell In Range("H1:H1000").Offset(1)
If Cell.Value <> "" Then
    Exit Sub
End If
Next Cell
For Each Cell In Range("H1:H1000").Offset(1)
If Cell.Value = "" Then
    Columns("H").Hidden = True
End If
Next Cell
Application.ScreenUpdating = False
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
This should do the trick.

  • It basically steps through columns 2, 4, 6 and 8 using the "For x=2 To 8 Step 2" line and uses the COUNTA formula on rows 2:1000.
  • When you see the . as in ".Range" or ".Cells" it means it's working with the "With ThisWorkbook" line and is the same as writing
    "ThisWorkbook.Worksheets("Sheet1").Range" or
    "ThisWorkbook.Worksheets("Sheet1").Cells".

    The line ".Range(.Cells(2, x), .Cells(1000, x))" is the same as saying "$B$2:$B$1000" when x = 2 (column 2).

    It's always best to be specific about which sheet you're working with.

Code:
Sub HideColumns()

    Dim x As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        For x = 2 To 8 Step 2
            If Application.WorksheetFunction.CountA(.Range(.Cells(2, x), .Cells(1000, x))) = 0 Then
                .Columns(x).Hidden = True
            End If
        Next x
    End With


End Sub

Edit: Took out comments and wrote in full instead.
 
Last edited:
Upvote 0
Darren,

Your explanation of the logic in this sub was just right for me to get my head around it. I changed the
Code:
[COLOR=#333333] With ThisWorkbook.Worksheets("Sheet1")[/COLOR]
to
Code:
With ActiveSheet
to make it easier to use on a day-to-day basis.

Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top