1

As a totally new VBA user (I'm actually not even an Excel user) I can't figure out how to do something that, I think, is really easy to achieve.

Datas

Consider a basic sheet with :

NAME | AGE | WEIGHT
Fred | 33  |  70
Bob  | 45  |  54
Jef  | 33  |  70

Aim

What I want is a Range that would contains all rows of that sheet IF the age==33 AND the WEIGHT==70. So the expected result is :

Fred | 33  |  70
Jef  | 33  |  70

My algorithm (as pseudo-code)

function filterMyRange(srcRange As Range) As Range

  myFinalRange = new Range()

  myWorksheet = Worksheets("worksheetName")
  for each row in myWorksheet.rows{
    age = row.column("AGE").value
    weight = row.column("WEIGHT").value
    if age == 33 AND weight == 77{
      myFinalRange.add(row)
    }
  }
  filterRange = myFinalRange

What I found

I found several topics to help me to reach my goal :

However, having to do it like this seams overcomplicated and I have the feeling that I'm doing it the wrong way.

Real situation

All previous sections were to explain my request. Actually, my real case is something like this :

A first sheet with :

worker_name | busy_day  | busy_start_time | busy_end_time
String      | Date      | Date            | Date
Fred        | 20/03/2020| 9:30:00         | 13:00:00
Bob         | 20/03/2020| 14:30:00        | 18:00:00

A second sheet with :

task_name | task_day  | task_start_time | task_end_time | is_Fred_available | is_Bob_available
String    | Date      | Date            | Date          | Boolean           | Boolean
task_01   | 20/03/2020| 10:30:00        | 11:30:00      | False             | True

In that second sheet, I want columns "is_Fred_available " and "is_Bob_available" automatically completed.

To do so, I'm trying to create a function isWorkerAvailableAt(workerName As String, day As Date, startTime As Date, endTime As Date). To reach that goal, while I like to split my functions, I'm trying to create an other function: findWorkerBusyTimes_byDay(workerName As String, day As Date). It's that function that is supposed to return the "filtered Range".

Actually, I could completely do not split my function isWorkerAvailableAt and so get around the problem. As a fan of "several small functions is better than a big one", it hurt's me a little bit but it might be the path.

Morgan
  • 589
  • 9
  • 20
  • [`Range.AutoFilter`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter) perhaps. – BigBen Mar 02 '21 at 15:18
  • @BigBen If I understand properly `Range.AutoFilter`, that method modify the visibility of cells. This is not my goal. What I want is to put that new range into in a variable. Nothing more. – Morgan Mar 02 '21 at 15:41
  • You need to loop then. What do you want to do with this range? This might be an XY problem. – BigBen Mar 02 '21 at 15:42
  • I did had a section to my question ("Real situation") where I do explain why I want to do it. – Morgan Mar 02 '21 at 17:47
  • 1
    Definitely an XY problem then. You probably don't even need VBA to accomplish your task, just a simple formula, probably IF and COUNTIFS. – BigBen Mar 02 '21 at 17:52
  • I will look a little more to COUNTIFS but it seams to do the trick ! Thanks ! However I'm steal curious. Is there any easy way to achieve what I was trying to do ? Basically a VBA equivalent to a SQL query like `SELECT * FROM mySheet WHERE age=33 AND weight=70`. – Morgan Mar 02 '21 at 19:59
  • An easy way? Not really, unfortunately. Well define easy. There are multiple ways to do it, but not necessarily easy ones. – BigBen Mar 02 '21 at 20:00
  • Ok. Thanks for your help ! – Morgan Mar 02 '21 at 21:41

0 Answers0