3

I want to create a function that, among others, has a range as input. This range will be 1 row by ? columns or 1 columns by ? rows. In other words, it will be "one dimensional" - either horizontal or vertical.

Based on whether it is H or V, certain calculations will be made. How can I determine if the range that is passed is horizontal or vertical?

Mierzen
  • 566
  • 1
  • 5
  • 25

2 Answers2

4

How about this:

Function RangeIsVertical(rng As Range) As Boolean
    RangeIsVertical = IIf(rng.Columns.Count = 1, 1, 0)
End Function

Sub Test()
    Debug.Print RangeIsVertical(Range("A1")) //True
    Debug.Print RangeIsVertical(Range("A1:A10")) //True
    Debug.Print RangeIsVertical(Range("A1:B2")) //False
End Sub
Alex P
  • 12,249
  • 5
  • 51
  • 70
1

If Range.Columns.Count=1 then it's vertical. If Range.Rows.Count=1, it's horizontal (assuming, like you said, you only have either rows or columns).

rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • Thanks! Looks like an awesome way of doing it. However, I made a testing range J34:J37 with values 1 2 3 4 and did Range.Width and .Height on that. It returned 30 and 54, respectively. Neither is equal to 1 :( – Mierzen Apr 18 '14 at 10:39
  • This refers to the pixel width and height. – enderland Apr 18 '14 at 11:30