Establishing a column number from a variable range

[Red over White]

I'm hoping someone will be able to help me with the following code

Sub HideCols()

Sheets("Sheet1").Activate

Dim Int1 As Integer
With Sheets("Sheet1")
.Columns("L:CR").EntireColumn.Hidden = False
For Int1 = 12 To 96
.Columns(Int1).Hidden = Cells(3, Int1).Value <> 0
Next Int1
End With

Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select

' Hide columns that aren't required

Dim rng As Range
Dim Col1 As Integer
Col1 = ActiveSheet.Columns(ActiveCell.Column).Rows(1)

' set rng to refer to required range

Set rng = Range(3 & ":" & Col1 - 1)

rng.Select
Selection.EntireColumn.Hidden = True

End Sub

I'm assuming the issue is surrounding Col1, in that it will be in the "A1" format, and should be a number. However, I don't know how to define Col1 as the identified column as a number.

Thanks

 

[Joe4]

See if this does what you want:

Sub MyHideCols()

    Dim c As Long

'   Find last column to hide
'   Check first to see if column N is blank in row 2
    If Cells(2, 14) = "" Then
'       If so, make column M the last column to hide
        c = 13
    Else
'       Otherwise, find the last populated column before the first blank column
'       after column M in row 2
        c = Cells(2, 13).End(xlToRight).Column
    End If
    
'   Hide columns 3 to last column above
    Range(Cells(2, 3), Cells(2, c)).EntireColumn.Hidden = True
    
End Sub

 

[Red over White]

Joe4

Thanks that's worked, and for your forebearance. I just had to add an extra line to finish it off on the actual file rather than the one I shared

Sub MyHideCols()

Dim c As Long

' Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select
' Find last column to hide
' Check first to see if column N is blank in row 2
' Replaced 14 with 47 and 46 with 13
If Cells(2, 47) = "" Then
' If so, make column M the last column to hide
c = 46
Else
' Otherwise, find the last populated column before the first blank column
' after column M in row 2
c = Cells(2, 46).End(xlToRight).Column
End If

' Hide columns 3 to last column above
Range(Cells(2, 3), Cells(2, c)).EntireColumn.Hidden = True

Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select

End Sub

I have learnt two things, the solution you have provided and a lot more preparation work is required before posting and perhaps provide an example

 

[Joe4]

You are welcome. Glad to hear that it worked for you.

Yes, the thing to remember is that while the question is very familiar to you, all that we know about it is what you share with us. Posting examples really helps in explaining and showing what you want. Once I saw your example, then it made sense to me what you were after.