Get column numbers of first and last columns in selected ran

Visions_Fugitive

New Member
Joined
Apr 15, 2002
Messages
18
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try the following:
Code:
Dim FirstCol, LastCol As String
FirstCol = Left(Selection.Address(columnabsolute:=False), 1)
LastCol = Right(Selection.Address(columnabsolute:=False), 1)
 
Upvote 0
Try this :-

Dim FirstCol%, LastCol%
FirstCol = Selection(1, 1).Column
LastCol = Range([A1], Selection).Columns.Count
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top