0

I want to create a range expression to use within Union() method.

I have this code so far:

Sub RangeExpresion()
Dim str As String, s

str = "2,10"
s = Split(str, ",")

' (1) If I do this it works. It selects rows 2 and 10
Union(Rows("" & s(0) & ""), Rows("" & s(1) & "")).Select

' (2) If I do this doesn't work. The expression is not correct. I get error
h = "Rows("" & s(0) & ""), Rows("" & s(1) & "")"
Union(h).Select

End Sub

As you can see, in first Union() command I use inside the expression Rows("" & s(0) & ""), Rows("" & s(1) & "") and is accepted and rows 2 and 10 are selected.

In second Union() I first try to store in a variable the same expression that works above, but this time I get error

argument not optional

How would be the way to do this? Thanks in advance.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ger Cas
  • 2,188
  • 2
  • 18
  • 45
  • 2
    I don't understand the question. You already seem to have working code using syntax (1)? NB: Easier is using `Union(Rows(s(0)), Rows(s(1)))` – jkpieterse Feb 28 '19 at 06:43
  • @GerCas Just in case `.Select` was not just for testing purpose you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 28 '19 at 07:43
  • @jkpieterse Thanks for the answer. The thing for I want to create an expression is because I obtain `str` from a previous process and in this case it has 2 rows but is variable, `str` could be for example `2,10,14,17,35` in that case would be `Union(Row(s(0),..., Rows(s(4)))`. How to make this dynamic if we have several rows? – Ger Cas Feb 28 '19 at 10:39
  • @Pᴇʜ thanks. Actually I put select for testing purposes hehe. – Ger Cas Feb 28 '19 at 10:39

2 Answers2

3

Union requires two Range objects as a minimum, not one string.

Your first attempt generates these as Rows is not encapsulated as string literal. Your second doesn't, so one string is passed, not two ranges.

Union

Returns the union of two or more ranges.

Syntax expression.

Union ( Arg1 , Arg2 , Arg3 , Arg4 , Arg5 , Arg6 , Arg7 , Arg8 , Arg9 , Arg10 , Arg11 , Arg12 , Arg13 , Arg14 , Arg15 , Arg16 , Arg17 , Arg18 , Arg19 , Arg20 , Arg21 , Arg22 , Arg23 , Arg24 , Arg25 , Arg26 , Arg27 , Arg28 , Arg29 , Arg30 )

Parameters

Name    Required/Optional   Data type   Description
Arg1    Required            Range       At least two Range objects must be specified.
Arg2    Required            Range       At least two Range objects must be specified.
Arg3    Optional            Variant     A range.

Requested example:

Option Explicit
Public Sub RangeExpresion()
    Dim str As String, s, rng1 As Range, rng2 As Range

    str = "2,10"
    s = Split(str, ",")
    Set rng1 = rows("" & s(0) & "")
    Set rng2 = rows("" & s(1) & "")

    Union(rng1, rng2).Select '<==Testing only. You should avoid .Select
End Sub

Though jkpieterse's comment of a better way is true. Union(Rows(s(0)), Rows(s(1)))

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Thanks for answer. Yes, I see the syntax, but then how can I do what I asking? How to pass a variable within `Union ()`. And why the negative vote to my question? – Ger Cas Feb 28 '19 at 06:35
  • I didn't downvote. I prefer to give comments on how to improve a question, or vote to close, over downvoting. But I assure you, it happens to all of us. – QHarr Feb 28 '19 at 06:36
  • create two range variables and pass those in. – QHarr Feb 28 '19 at 06:38
  • Then somebody unknown voted negative and I don't know why? I only asking for help doing a question. – Ger Cas Feb 28 '19 at 06:40
  • May you share an example of a range variable please. – Ger Cas Feb 28 '19 at 06:42
  • _Then somebody unknown voted negative and I don't know why?_ Yes. Unless they are thoughtful enough to leave a comment. Example given. – QHarr Feb 28 '19 at 06:50
  • thanks for the example. The issue I have is how to do if I have several rows in variable `str`, let's say 5 or 10? Since `str` is obtained from a previous process. – Ger Cas Feb 28 '19 at 10:42
  • In the way you have Union(Rows(s(0)), Rows(s(1))) , where s is generated from split on str – QHarr Feb 28 '19 at 10:49
1

You would have to build the unified range dynamically using a loop over the s array:

Sub RangeExpresion()
    Dim str As String
    Dim s As String
    Dim lItem As Long
    Dim rUnifiedRange As Range
    str = "2,10"
    s = Split(str, ",")
    For lItem = LBound(s) To UBound(s)
        If rUnifiedRange Is Nothing Then
            Set rUnifiedRange = Rows(s(lItem))
        Else
            Set rUnifiedRange = Union(rUnifiedRange, Rows(s(lItem)))
        End If
    Next
End Sub
jkpieterse
  • 2,727
  • 1
  • 9
  • 18