Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date? | Excel Programming | |||
convert "15000" to "Fifteen thousand" in excel cell? | Excel Worksheet Functions | |||
Excel macro convert to VBA - doesn't work, hangs on Range("Q35").Select | Excel Programming | |||
Convert range values to correct "hh:mm:ss" format. | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |