Convert from ColumnNumber to Range(" ")
 

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

Convert from ColumnNumber to Range(" ")
 

On Jul 16, 3:24*pm, 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

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

Convert from ColumnNumber to Range(" ")
 

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

Convert from ColumnNumber to Range(" ")
 

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" wrote in message
...
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

Convert from ColumnNumber to Range(" ")
 

ker_01 - The following function is simpler and more easily adapted to
any version of Excel

Public Function ColNumToAlpha(iCol As Integer)

'Test to see if iCol is greater than the number of columns your
version of Excel supports
If iCol 256 Then
MsgBox "Number entered is greater than max number of columns in
this version of Excel"
ColNumToAlpha = "Bad iCol Value"
Exit Function
End If

ColNumToAlpha = Columns(iCol).Address

ColNumToAlpha = Mid$(ColNumToAlpha, 2, Len(ColNumToAlpha) -
InStr(ColNumToAlpha, ":") - 1)

End Function


Brian B -

Using the above and bits of Rick's code gets us to:

Public Sub test()


Dim iColNum As Integer

On Error Resume Next

iColNum = Application.InputBox(prompt:="Including Col C, hide this
many columns:", Type:=1)

Columns("C:" & ColNumToAlpha(iColNum)).Hidden = True


End Sub


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116866