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

On Jul 16, 3:24*pm, Brian B <Bri...@discussions.microsoft.com> wrote:
> 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

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

"Brian B" wrote:

> 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

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.

--
Rick (MVP - Excel)


"Brian B" <(E-Mail Removed)> wrote in message
news:8DA01C73-0A03-4A5E-92E5-(E-Mail Removed)...
> 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