0

I did a little research and I found my question. The problem is it is written for some language called C that I am not familiar with. Not that I am all that familiar with VBA either but that is what I am working in and doing a very nice job of screwing things up. So in an effort to thwart my abilities to screw things up and instead make things work, how do I pass a sub range or column from a user selected column?

so in my example code I was trying to pass the entire first column of whatever was selected. Lets say user range was B34:D40, I want to pass B34:B40 such that arrange is B34:B40.

Sub initialsub (userrange as range)

Call secondfunction (userrange([],1)

end sub
----------------------------------------------------------------
function secondfunction (arrange as range) as long

some function that counts a variety of things in a single column

secondfunction = 45

end function
Community
  • 1
  • 1
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • hmm. So if range is `A10:B20`, you want `A10:A20`? – findwindow Apr 26 '16 at 22:55
  • correct! or if the user selected B34:D50, I want to pass in the call either through another variable, a function, or syntax B34:B50. I just got rid of using the A column since it is column 1 in absolute reference as well as relative. – Forward Ed Apr 26 '16 at 23:01

1 Answers1

1

One way:

Sub dural()
    Dim FirstColumn As Range

    Set FirstColumn = Intersect(Selection(1).EntireColumn, Selection)
    MsgBox FirstColumn.Address
End Sub

enter image description here

Also:

Sub dural()
    MsgBox Selection.Columns(1).Address
End Sub

seems to do the trick.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • is there a way without using selection? Right now I am testing the code by calling it from a sub and passing it a hard coded range. There for with my test sub there is no selection made. I do like this method when an actual selection is available. And I do agree this is exactly what I stated in the title of the question! – Forward Ed Apr 26 '16 at 23:15
  • would `intersect(userrange(1).entirecolumn,userrange)` if I was passing B34:D40 as userrange? Would the intersect result in B34:B40? – Forward Ed Apr 26 '16 at 23:22
  • Good point about the block style range...but since that is my case THIS TIME, I will be selfish and accept this as the answer. 8) What happens if you just used `userrange(1).entirecolumn` would it return the entire column of just the urserrange or will it take the entire column of the worksheet? – Forward Ed Apr 26 '16 at 23:26
  • @ForwardEd It would return the entire column, but..................see my little edit. – Gary's Student Apr 26 '16 at 23:28
  • hrrmmm so if I have to pass the range, would I use `userrange.Columns(1).address` or `userrange.columns(1)` in the function call? – Forward Ed Apr 26 '16 at 23:33
  • 1
    @ForwardEd Since you want to do stuff with the individual cells in that first column, I personally would pass `userrange.Columns(1).Cells` ............this will insure that Excel treats it as a Range Object rather than a Columns Object. – Gary's Student Apr 26 '16 at 23:37
  • `.cells` is definitely the key. I tried address. that blew up on me and gave an error on type mismatch. I tried just `.column(1)`, and that did not give an error, but it also did not give the right results! Thanks for the help on this one Gary. – Forward Ed Apr 26 '16 at 23:39