Need help with Procedure to return selected column numbers

D

Dan Thompson

I am having trouble with this macro

Sub ReturnColumnNumbers()
Dim ColNum As Integer
ColNum = Selection.Column
MsgBox ("The Selected Column Numbers are" & Chr(13) & ColNum)
End Sub

The problem is it works when I select just one column but if I select more
than one column it does not return the other column numbers ?

Any thoughts ?

Dan Thompson
 
P

Peter T

Another one -

Sub test2()
Dim nColFirst As Long, nColLast As Long
Dim sMsg As String
Dim ar As Range

Range("B2:B9,D2:E9,G2:I9").Select

For Each ar In Selection.Areas
With ar
nColFirst = .Column
nColLast = nColFirst + .Columns.Count - 1
If nColLast = nColFirst Then
sMsg = sMsg & nColFirst & " "
Else
sMsg = sMsg & nColFirst & ":" & nColLast & " "
End If
End With
Next

MsgBox sMsg
End Sub

Regards,
Peter T
 
D

Dave Peterson

One more...

Option Explicit
Sub testme()

Dim myStr As String
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = Intersect(Selection.EntireColumn, .Rows(1))
End With

myStr = ""
For Each myCell In myRng.Cells
myStr = myStr & ", " & myCell.Column
Next myCell

myStr = Mid(myStr, 3)

MsgBox myStr
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top