VBA to extract records based on two conditions

[Lifeseeker]

Hi,

I would like to use vba or macro to extract a list of records based on condition. Condition is...reporting period is Q 1 Period 3 and CCM patient = "Yes".

I have attached the file in the post as well.

Please could someone help me with vba on this one? I'm really stuck...

Thank you

[tigeravatar]

Lifeseeker,

Something like this?

Please Login or Register  to view this content.

[Lifeseeker]

Lifeseeker,

Something like this?

Please Login or Register  to view this content.

Thank you Tigeravatar.

If I wanted to know how many records are actually filtered, is it also possible to somehow add a total sum at the end of the filtered record set, or create a msgbox that says....x number of records are filtered when I run the macro?

Thank you again, it's really helpful.

[tigeravatar]

Lifeseeker,

Please don't quote whole posts (see forum rules, link in my sig). As to your question, here's an updated version of the code:

Please Login or Register  to view this content.

[Lifeseeker]

Would it make a big difference to the syntax of the code if the reporting period is changed to dropdown? It would be good if we had the ability to pick and choose which reporting period we want to filter the record on.

Thank you for your help.

[tigeravatar]

Lifeseeker,

At that point you're doing one of the following:
1) The drop-down is in a worksheet cell, so the code becomes a Worksheet_Change event
2) The drop-down is in a sheet control (form or activex), so the code becomes a Control_Change event
3) The drop-down is in a userform, so the code becomes part of the userform

You'll need to decide which method you'd prefer before you can proceed. The easiest one to implement is the worksheet cell containing a validation list.

[Lifeseeker]

Hi,

I think the first option is sufficient enough.

I have moved the code to the worksheet_change event, but I wasn't sure if I should still write " .AutoFilter RptField, "Q 1 Period 3"" in the code. I am suspecting it will be modified as well?

I have re-attached the file as well.

[tigeravatar]

Lifeseeker,

In the new attachment I see the drop-down list is in cell Z1. It was kind of hidden because it had a button over it, so I had to move the button to be able to use the drop-down list. After having found it, I altered the code to suit the worksheet_change event as follows:

Please Login or Register  to view this content.

[Lifeseeker]

Tigeravatar,

So if I wanted to have a button that invokes that method, would I be able to do it from the spreadsheet? I have a button(form control).

K1 is where the button is.

I have re-attached the file.

Thank you

[tigeravatar]

Lifeseeker,

If your dropdown list is in cell Z1, i'm not sure why the button to use it is in cell K1. The button isn't really needed because the code runs as soon as Z1 changes. If you really want the button, you would change Z1, and then click the button to run this code (NOTE: you should remove the worksheet_change event if you do it this way):

Please Login or Register  to view this content.

[Lifeseeker]

Tigerzaratar,

Could you explain what "Z1" is?

In this file I'm sending you now, the button is at the top of AB2, and the reference for the dropdown list is in AU as you can see. (sorry I rearranged cell columns, but the idea is the same).

I'm not sure what you meant when you said the dropdown list is in "Z1". Shouldn't the dropdown list be a range as well and not limited to a single cell?

Thank you for your patience.

[tigeravatar]

Lifeseeker,

Z1 was the cell that contained the drop-down list. From what you're saying though, you want to filter for all items in column AU? Try this:

Please Login or Register  to view this content.

[Lifeseeker]

Thank you Tiger. I haven't tried the code yet, but just a thought that came to me as I was dwelling on it.

When the filter is applied(or when the button is pressed), does it ALWAYS lock the filtered recordset?

I've noticed that Excel won't let me browse through the filtered record if I wanted to, but how much code change is required to make filtered recordset browsable?

Thanks again

[Lifeseeker]

Hi Tiger,

The post might have got buried, but did you see my last post? The code works out great.

Thank you

[tigeravatar]

Lifeseeker,

I'm confused by these statements:
"lock the filtered recordset"
"Excel won't let me browse through the filtered record"

What, exactly, are you trying to do that it won't let you? The result should be the sheet, filtered, showing you the items you asked it to show you. You should be able to click on the items, update/change them, copy/paste them, etc. What do you mean by "browse" or that its "locked"??

[Lifeseeker]

Hi Tiger,

Sorry I wasn't clear.

I found an error in the application by the way. The dropdown list is now in "AD1".

When I run the code, it always gives me no records even though one of the rows should have been filtered. I couldn't identify the cause and I am wondering if you can help me.

As for the locked filtered recordset....after you click on the button to filter the results, the screen locks up, preventing you to select, copy or paste the filtered resordset. I would like to have the ability to at least select the filtered rows to copy. Is this possible?

Thank you and I have re-attached the file.

[tigeravatar]

Lifeseeker,

When you change the columns, you need to update the code so it knows what columns to use. In the new workbook, it should be:

Please Login or Register  to view this content.

So just keep an eye on that. As for the workbook locking up, I am not experiencing that problem, so I'm not sure what advice to give

[Lifeseeker]

Ahh I see. The screen locks up because I didn't change the code to reflect the column change.

If you wouldn't telling me....what do the following codes do? I can't seem to quite make out what that "+1" represent...

Please Login or Register  to view this content.

[tigeravatar]

When you .AutoFilter on a range, you have to tell the filter what field (column) to filter on. The field numbers start at 1 where 1 is the first column in the range. So if the range is from V:Z, then field 1 = column V.

Knowing that, let's look at that code:

Please Login or Register  to view this content.

• RptPrdCol is a column letter, which in the latest case is column AD
• CCMPatCol is a column letter, which in the latest case is column Y
• RngRecords is a Range object, which is: Columns(RptPrdCol & ":" & CCMPatCol), or in other words Columns("AD:Y") which = Columns("Y:AD") (which is how it would normally be read)

Knowing the definitions of those variables, we can understand the calculation happening:

• The range is from Y to AD. That's a total of 6 columns including Y and AD
• When asking for RngRecords.Column, it will only give the first column, Y, which is 25.
• So columns(AD).column (30) - RngRecords.Column (25) + 1 = 6, which is the correct field number for the .AutoFilter that will be applied later

[Lifeseeker]

Hi Tiger,

I just added another filter in the spreadsheet, and when I click on the new button(CT Head check), the count box no longer appears telling me how many records have been filtered. Dropdown column is now at "BB1".

CCM check works as before, but not the CT Head button. These two are separate analyses, looking at different columns, but the idea is the same.

Does this have to do with the autofilter?

I'm re-attaching the file.

Thank you

[Lifeseeker]

When you .AutoFilter on a range, you have to tell the filter what field (column) to filter on. The field numbers start at 1 where 1 is the first column in the range. So if the range is from V:Z, then field 1 = column V.

Knowing that, let's look at that code:

Please Login or Register  to view this content.

• RptPrdCol is a column letter, which in the latest case is column AD
• CCMPatCol is a column letter, which in the latest case is column Y
• RngRecords is a Range object, which is: Columns(RptPrdCol & ":" & CCMPatCol), or in other words Columns("AD:Y") which = Columns("Y:AD") (which is how it would normally be read)

Knowing the definitions of those variables, we can understand the calculation happening:

• The range is from Y to AD. That's a total of 6 columns including Y and AD
• When asking for RngRecords.Column, it will only give the first column, Y, which is 25.
• So columns(AD).column (30) - RngRecords.Column (25) + 1 = 6, which is the correct field number for the .AutoFilter that will be applied later

Hi Tiger,

How much code change is involved if I decide to do everything on this form? Will it look much different?

So essentially we are passing parameters through form. The dropdown will now be on this userform and when I click on a button on the form, the spreadsheet filters record based on the reporting period I specify?

Thank you