VBA - Sub to combine four other subs

[smitty120]

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!

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

 

[Darren Bartrup]

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.

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.

 

[smitty120]

Darren,

Your explanation of the logic in this sub was just right for me to get my head around it. I changed the

[COLOR=#333333] With ThisWorkbook.Worksheets("Sheet1")[/COLOR]

to

With ActiveSheet

to make it easier to use on a day-to-day basis.

Thanks for your help!

 

[Darren Bartrup]

Glad I could help and thanks for the feedback.