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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
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:

smitty120

New Member
Joined
Feb 18, 2014
Messages
9
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!
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
Glad I could help and thanks for the feedback. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,752
Messages
5,524,631
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top