AndrePH AndrePH - 8 months ago 37
R Question

How can I replicate R subset mechanism in Excel VBA?

First of all thank you for reading my post.

I would like to ask how can I replicate R subset mechanism in ?

Here is my function:

Subdeck2 = deck2[(deck2[,3]>=10 & deck2[,4]<=30),]

The code uses to create a
object called
which is a subset of a
object called
that contain the rows of
that have a third column value of more than or equal to ten, and a fourth column value of less than or equal to thirty.

I would like to replicate this in , and a worksheet that is a subset of a the worksheet with the source data. I think the array naming in is very helpful to reference the rows and columns.

In , it tends to get confusing when I have to do this repeatedly, because I have to remember the row and column numbers that I have already input.

I only need to do this one particular thing in . I already bought a book about programming but it's like 1000 pages long and I cant seem to find the word subset in there.

Any suggestions on how to do this or where i can learn to do this will be very appreciated. Thanks!


Here is an example - nowhere near as concise as your function though.

The method is commented - but basically, it iterates the rows of the source range and checks each row for the criteria. Then it selects the output range and resizes it to the size of the filtered data before output.

Option Explicit

Sub FilterLikeRSubset()

    Dim rngData As Range
    Dim rngRow As Range
    Dim rngFilter As Range
    Dim rngOutput As Range

    'get data
    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:D5")

    'iterate rows in data
    For Each rngRow In rngData.Rows
        'test row criteria
        If rngRow.Cells(1, 3) >= 10 And rngRow.Cells(1, 4) <= 30 Then
            If rngFilter Is Nothing Then
                Set rngFilter = rngRow
                Set rngFilter = Union(rngFilter, rngRow)
            End If
        End If
    Next rngRow

    'set range for output
    Set rngOutput = ThisWorkbook.Worksheets("Sheet1").Range("A10")
    Set rngOutput = rngOutput.Resize(rngFilter.Rows.Count, rngFilter.Columns.Count)

    rngOutput.Value = rngFilter.Value

End Sub

Sample output:

enter image description here