Selecting the secon column of a separate range

[filo666]

Hello, I'm working with graphs, what I want to do is a kind of difficult to
explain, I hope you understand what I'm trying to say.

I have The following data:

a b c d
1 Time Flow pressure permeate
2 1 3 40 .23
3 2 3.2 40.1 .24
4 3 3.2 40 .2

and so on

I press the control key in the cell A1 and I left clik and slide the pointer
from a1 to a4; then I stop pressing the left key of my mouse and go to the
cell c1 and presing the key control I repeat the same operation; then I have
a range composed by two different columns; wath I want VB to do is to tell me
the column number of the first and second selected columns (and thirt,
fourth, or any amount of columns selected)

I must mention that the ranges are allways from left to right and not at the
bottom (and all the ranges are allineated).

what I tried:

g = Selection.Columns.Count; it gives me the number 1 and that is wrong
because I have 2 columns (and I was expecting the value of column c=3).

the range is:

"a1:a4,c1:c4" and not "a1:c4"

I hope you understood.

TIA

 

[JP]

Are you saying that you are selecting two separate areas of your
worksheet using Ctrl-Click(drag)?

If you highlight A1:A4 and C1:C4 and both are selected,
Selection.Areas.Count will give you the correct number (2).


HTH,
JP

 

[filo666]

that is what I wanted to explain; what I want is the column number and not
the number of columns. (1 for the range a1:a4 and 3 for the range c1:c4.

Thanks

 

[JP]

Try this (note: I didn't write the function, only the sub). Select
A1:A4 and C1:C4 so that both are selected, then run GetRangeNumbers()
and you will get a message box with the column numbers for each
column.

Sub GetRangeNumbers()
Dim rng As Excel.Range
Dim X As Excel.Range
Set rng = Selection
For Each X In rng.Areas
MsgBox ConvLtrToNum(Mid(X.Cells(1, 1).Address, 2, 1))
Next X
End Sub

Private Function ConvLtrToNum(ByVal LtrIn As String) As Integer
Dim TempChar As String
Dim TempNum As Integer
Dim NumArray() As Integer
TempChar = ""
TempNum = 0
LtrIn = UCase(LtrIn)
For i = 1 To Len(LtrIn)
NumString = ""
' Change Ltr to Number Indicating Place in Alphabet from 01 to 26
TempChar = Mid(LtrIn, i, 1)
ReDim Preserve NumArray(i)
NumArray(i) = Asc(TempChar) - 64
Next
' Our Most Significant Digits Occur to the Left
HighPower = UBound(NumArray()) - 1
' Convert the Number Array using Powers of 26
For i = 1 To UBound(NumArray())
TempNum = TempNum + (NumArray(i) * (26 ^ HighPower))
HighPower = HighPower - 1
Next
ConvLtrToNum = TempNum
End Function


HTH,
JP