Code to hide columns where row 3 is blank

[WStockel]

In a range from B:BU I'm trying to find the first blank in row 3 and hide that column to column BU. Below is my code. I used the same code on another sheet and it worked fine.

Private Sub CBExit_Click()
Dim col As Integer

For col = 2 To 72
If Worksheets("Results").Cells(3, col).Value = "" Then
Columns(col).Hidden = True
End If
Next

End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub CBExit_Click()
    Dim col As Long
    For col = 72 To 2 Step -1
     With Worksheets("Results").Cells(3, col)
            .EntireColumn.Hidden = CBool(Len(.Value) = 0)
     End With
    Next
End Sub

Dave

 

[WStockel]

Hi,
see if this update to your code does what you want

Private Sub CBExit_Click()
    Dim col As Long
    For col = 72 To 2 Step -1
     With Worksheets("Results").Cells(3, col)
            .EntireColumn.Hidden = CBool(Len(.Value) = 0)
     End With
    Next
End Sub

Dave

Thanks Dave. That worked. Never heard of CBool before. Guess that's what you get when you are self taught.

 

[dmt32]

Thanks Dave. That worked. Never heard of CBool before. Guess that's what you get when you are self taught.

Its a Function that coerces the expression to the required data type (Boolean True / False)
Further info can be found in VBA helpfile.

Glad suggestion helped

Dave

 

[Peter_SSs]

I'm trying to find the first blank in row 3 and hide that column to column BU

That description implies that as soon as you find the first blank cell (from B3 looking at your code) across then you hide that column and all the rest to column BU. If that is the case and those cells are truly blank (that is do not contain formulas returning "") then you could hide them all in one go without looping.

Private Sub CBExit_Click()
  With Sheets("Results")
    On Error Resume Next
    .Range(.Range("B3:BU3").SpecialCells(xlBlanks).Cells(1), .Range("BU3")).EntireColumn.Hidden = True
    On Error GoTo 0
  End With
End Sub