Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Get column numbers of first and last columns in selected ran

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Milwaukee, WI
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a macro that needs to loop through all the selected columns, performing an operation on each of them. I just need a way to get the numbers of the first and last columns.

    firstCol = Selection.??????
    lastCol = Selection.??????

    For x = firstCol To lastCol

    Columns(x).TextToColumns DataType:=xlDelimited, _
    ConsecutiveDelimiter:=False, Space:=False

    Next x


    Thanks in advance.


    [ This Message was edited by: Visions_Fugitive on 2002-04-16 14:39 ]

    [ This Message was edited by: Visions_Fugitive on 2002-04-16 14:39 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:
    Code:
    Dim FirstCol, LastCol As String
    FirstCol = Left(Selection.Address(columnabsolute:=False), 1)
    LastCol = Right(Selection.Address(columnabsolute:=False), 1)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3

    Join Date
    Feb 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this :-

    Dim FirstCol%, LastCol%
    FirstCol = Selection(1, 1).Column
    LastCol = Range([A1], Selection).Columns.Count

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Milwaukee, WI
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your help, Al and Bertie.

    For the record, Bertie's code worked. My macro now runs text to columns on all selected columns. It's very useful for data that comes from Access. Often Access will store numeric data as text. Formatting the cells as numbers and running Text to Columns fixes this.

    Here is the full macro, in case anyone would like it:

    Code:
    Sub TTC()
        
        Dim FirstCol%, LastCol%
        FirstCol = Selection(1, 1).Column
        LastCol = Range([A1], Selection).Columns.Count
        
        For x = FirstCol To LastCol
         
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Columns(x).TextToColumns DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Space:=False
            
        Next x
        
    End Sub

    [ This Message was edited by: Visions_Fugitive on 2002-04-17 07:17 ]

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My code works if you select the whole column, but if you don't select the whole column try the following code:
    Code:
    Dim FirstCol, LastCol As String
    FirstCol = Left(Selection.EntireColumn.Address(columnabsolute:=False), 1)
    LastCol = Right(Selection.EntireColumn.Address(columnabsolute:=False), 1)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Milwaukee, WI
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My code works if you select the whole column, but if you don't select the whole column try the following code:
    Al, I was getting a type mismatch error with your code when my loop started. I tried it without dimensioning the variables and the same thing happened. Perhaps if I dimmed the variables as integers it would have worked.

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would have to see your code, but if you just select, for example, A1:H5 then the following code will return:
    FirstCol=A
    LastCol=H
    Code:
    Dim FirstCol, LastCol As String
    FirstCol = Left(Selection.EntireColumn.Address(columnabsolute:=False), 1)
    LastCol = Right(Selection.EntireColumn.Address(columnabsolute:=False), 1)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  8. #8

    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 09:25, Visions_Fugitive wrote:
    My code works if you select the whole column, but if you don't select the whole column try the following code:
    Al, I was getting a type mismatch error with your code when my loop started. I tried it without dimensioning the variables and the same thing happened. Perhaps if I dimmed the variables as integers it would have worked.

    For Al Chara's code, you need to Dim the LastCol and First Col As String (as opposed to Bertie Bagshot's code which is "dimmed" as Integer).

    You should have got a type mismatch error on Al Chara's code for LastCol only, not for Firstcol, since in the code he posted LastCol was dimmed as string while FirstCol was not assigned a variable type so has the default type Variant(which covers all variable types).

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sub firstlastcolumn ()
    dim intfirstcolumn as integer
    dim intlastcolumn as integer
    dim i as integer
    ' you need to make selection now..

    ' or you can select current region

    ' currentregion.select

    intfirstcolumn = Activecell.column
    intlastcolumn = selection.column.count + intfirstcolumn

    ' you can step for to get operation by leaving one column or two column.
    for i = intfirstcolumn to intlastcolumn

    cells(1,i) = "Put your operation."

    next i

    end sub


    ni****h desai
    http://www.pexcel.com

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sub firstlastcolumn ()
    dim intfirstcolumn as integer
    dim intlastcolumn as integer
    dim i as integer
    ' you need to make selection now..

    ' or you can select current region

    ' currentregion.select

    intfirstcolumn = Activecell.column
    intlastcolumn = selection.column.count + intfirstcolumn

    ' you can step for to get operation by leaving one column or two column.
    for i = intfirstcolumn to intlastcolumn

    cells(1,i) = "Put your operation."

    next i

    end sub


    ni****h desai
    http://www.pexcel.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •