CODEWITHSUNDEEP
excelvbarange
baarkerlounger
baarkerlounger

Reputation: 1226

VBA - How to set the start and end row when setting a range by column numbers

Suppose I set a range of multiple distinct columns by column number like this:

Set rngData = shtData.Range(Columns(1), Columns(2), Columns(5))

But I only want the range to start at for example row 5 and end at row 10.

Can I specify this in the Set rngData statement? Appreciate any help

EDIT.

Thanks to KazJaw this is the solution that worked for me:

    Dim shtData, rngData
    Set shtData = Worksheets("Data")
    Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))
    Set rngData = Intersect(rngData, shtData.Rows("5:10"))

Upvotes: 1

Views: 8402

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

There are few possible ways but I'm not sure which would be best for you. My first and possibly simplest proposal is the following:

Set rngData = shtData.Range(Columns(1), Columns(2), Columns(5))
set rngData = Intersect(rngData, shtData.Rows("5:10"))

But, your current range will not work anyway. You could use something like this instead:

Set rngData = shtData.Range("A:B, E:E")

or if you need to keep numeric index of columns than you could go this way (complete code):

Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))
set rngData = Intersect(rngData, shtData.Rows("5:10"))

Edit- to extend and explain: See below subroutine and comments inside

Sub Test_Selection()
'Variant A- select range from column 1 to 5
    ActiveSheet.Range(Columns(1), Columns(5)).Select

'Variant B- select range of columns: 1, 2 and 5
    With ActiveSheet
        Union(.Columns(1), .Columns(2), .Columns(5)).Select
    End With
'This will not work
    ActiveSheet.Range(Columns(1), Columns(2), Columns(5)).Select
End Sub

So, variant B above is able to select not continuous range like this:

enter image description here

And final explanation about Intesect, for all who are interested. This procedure would give result as presented in the picture below:

Sub Final_Combination()
    Dim shtData As Worksheet
        Set shtData = ActiveSheet
    Dim rngData As Range
    Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))

    Set rngData = Intersect(rngData, shtData.Rows("5:10"))

'or simply combined in one line of code
    Set rngData = Intersect(Union(shtData.Columns(1), _
                                shtData.Columns(2), _
                                shtData.Columns(5)), _
                            Rows("5:10"))
        rngData.Select
End Sub

enter image description here

Upvotes: 2

Related Questions