Get column numbers of first and last columns in selected ran

[Visions_Fugitive]

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 ]

[Al Chara]

Try the following:

Dim FirstCol, LastCol As String
FirstCol = Left(Selection.Address(columnabsolute:=False), 1)
LastCol = Right(Selection.Address(columnabsolute:=False), 1)

[Bertie Bagshot]

Try this :-

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

[Visions_Fugitive]

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:

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 ]

[Al Chara]

My code works if you select the whole column, but if you don't select the whole column try the following code:

Dim FirstCol, LastCol As String
FirstCol = Left(Selection.EntireColumn.Address(columnabsolute:=False), 1)
LastCol = Right(Selection.EntireColumn.Address(columnabsolute:=False), 1)

[Visions_Fugitive]

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.

[Al Chara]

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

Dim FirstCol, LastCol As String
FirstCol = Left(Selection.EntireColumn.Address(columnabsolute:=False), 1)
LastCol = Right(Selection.EntireColumn.Address(columnabsolute:=False), 1)

[Abdc]

On 2002-04-17 09:25, Visions_Fugitive wrote:

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).

[nisht]

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


nishith desai
http://www.pexcel.com

[nisht]

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


nishith desai
http://www.pexcel.com