Convert from ColumnNumber to Range(" ")

[Brian B]

Hello,
I'm having trouble with what I would think is a very simple issue. I would
like to hide a number of columns but I would like it to be subject to user
input.

Here's the very simple code that cuts out the user input:

Columns("C:AH").Select
Selection.EntireColumn.Hidden = True

But I want column "AH" to be user selectable. The User would input the
number '36' (the number of columns over from the left "AH" is) and that's how
C:AH would be hidden.
How do I convert '36' into a Column Range unit? I've been messing around
with As Range and haven't been real lucky.

Thanks in advance for any assistance,
Brian

 

[Matthew Herbert]

Hello,
I'm having trouble with what I would think is a very simple issue.  I would
like to hide a number of columns but I would like it to be subject to user
input.  

Here's the very simple code that cuts out the user input:

Columns("C:AH").Select
Selection.EntireColumn.Hidden = True

But I want column "AH" to be user selectable.  The User would input the
number '36' (the number of columns over from the left "AH" is) and that'show
C:AH would be hidden.  
How do I convert '36' into a Column Range unit?  I've been messing around
with As Range and haven't been real lucky.  

Thanks in advance for any assistance,
Brian

Brian,

I'm sure there are MANY ways to do this; I've listed one way below.
(Be sure to qualify your ranges though).

Best,

Matthew Herbert

Dim varInput As Variant
Dim intCnt As Integer
Dim intStartCol As Integer

intStartCol = Range("C1").Column

'You can change the type if you want, i.e. see the VBE Help for
' InputBox Method.
varInput = Application.InputBox("Enter the number of columns to
hide.", _
"Column Hide", Type:=1)

varInput = CInt(varInput)

For intCnt = 1 To varInput
With Cells(1, intStartCol).Offset(0, intCnt)
.EntireColumn.Hidden = True
End With
Next intCnt

 

[ker_01]

Something I picked up in this newsgroup years ago (and apologies to whomever
posted it, I've long since forgotten so I can't give you the credit that is
due). This works in 2003; you would need to edit it a little to pull in
triple-letter columns for 2007 (I suspect that code has been posted as well,
but I try to stay in 2003 as much as possible)

HTH
Keith

Function ConvertCol(SourceNum)

MyColNum = SourceNum
'==================================================================
'Translate Column header to usable letter as UseCol

ColMod = MyColNum Mod 26 'div column # by 26. Remainder is the
second letter
If ColMod = 0 Then 'if no remainder then fix value
ColMod = 26
MyColNum = MyColNum - 26
End If
intInt = MyColNum \ 26 'first letter
If intInt = 0 Then Usecol = Chr(ColMod + 64) Else _
ConvertCol = Chr(intInt + 64) & Chr(ColMod + 64)
'==================================================================

End Function

 

[Rick Rothstein]

How about this way... it asks the user which column letter(s), NOT which
column number, he/she wants to hide up to starting at Column C...

WhichCol = InputBox("What column letter(s) do you want to hide up to?")
Columns("C:" & WhichCol).Hidden = True

Note there is no error checking in this code to make sure the column is at
least Column C and no larger than the letter designation of the user's
version of Excel... you should incorporate some in your actual code.