AndrePH - 9 months ago 42

R Question

First of all thank you for reading my post.

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

Here is my r function:

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

The code uses r to create a

`data.frame`

`Subdeck2`

`data.frame`

`deck2`

`deck2`

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

In r, 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 excel. I already bought a book about vba 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!

Answer

Here is an example - nowhere near as concise as your r 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
'success
If rngFilter Is Nothing Then
Set rngFilter = rngRow
Else
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)
'output
rngOutput.Value = rngFilter.Value
End Sub
```

Sample output:

Source (Stackoverflow)