Filtering problems with VBA in Excel

Question

Not sure why, but I cannot type in all of the information I wanted to. I am guessing there is a limit to how many lines can be added here, so I have created a WORDdocument with a full description of the issues I am having with the above filtering problem.

I also added a copy of the EXCELfile I am having the issues with.

Please let me know if there are any problems with accessing these files, or if more clarity is needed on the problem I am having.

Thank you for your time.

Answer 1

Hi Jeovany,

I am ok with a Zoom or Teams video chat. Thank you.

An update from when I posted my reply to Jeovany. I was able to get the filtering to work. I had to move all of the Sub Routines into one. Here is a revised file if you wish to look at the new code. It isn't near as easy to ready, but most of the filtering is working.

The area I am still having a problem with is in this section.

' Generate UPU41_Cond file with filter for column H starting with "2"

Set newWb = Workbooks.Add 

Set docSenseLabel = newWb.SensitivityLabel 

Set labelInfo = docSenseLabel.CreateLabelInfo() 

dataCopied = CopyColumns(ws, newWb.Sheets(1), columns3, lastRow,"H","2\*", "AK", "6.08","28.58") 

    With labelInfo 

            .AssignmentMethod = PRIVILEGED 

            .LabelId = "57443d00-af18-408c-9335-47b5de3ec9b9" 

            .labelName = "GENERAL" 

            .SiteId = "6e51e1ad-c54b-4b39-b598-0ffe9ae68fef" 

    End With 

    docSenseLabel.SetLabel labelInfo, labelInfo 

    If dataCopied Then 

        newWb.SaveAs Filename:=filePath1 & "UPU41\_Cond" & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:= \_ 

        xlOpenXMLWorkbook, CreateBackup:=False 

    End If 

newWb.Close 

With the secondary filter on column "AK" and the values of "6.08" or "28.58", I am still getting multiple results. The row that has a blank value should not be copied into this file. It would be better if I could do the secondary filter with a wildcard or a "Not Blank", as this value may change.

WERKS	MATNR	LIFNR	EKORG	KSCHL	KBETR
US38	MH68D9VWP	2SG02011	USAA	PB00	28.58
US38	MH74D9VWP	2US00080	USAA	PB00
US38	MH80D9VWP	2FR08021	USAA	PB00	6.08

Answer 2

Hi Darrens

I want to help you with your problem, I got your Excel file and Word document.

But, it would be better if we could organize a video chat via Zoom or Teams, as your project is very complex.

I also need to know about your data entry process and final goals with the template.

Let me know if that's OK with you. So I can send you the video link.

Regards

Jeovany

Answer 3

I have worked on this some more and I think I know where the problem may be, but can't figure out how to fix it.

There are some parameters set up for filters in a Fub routine and a Function. Here are the lines.

Sub GenerateFile(ws As Worksheet, columns As Variant, lastRow As Long, fileName As String, Optional filterColumn As String = "", Optional filterValue As String = "", Optional secondaryFilterColumn As String = "", Optional secondaryFilterValue1 As String = "", Optional secondaryFilterValue2 As String = "", Optional addSequence As Boolean = False)

Function CopyColumns(srcWs As Worksheet, destWs As Worksheet, columns As Variant, lastRow As Long, Optional filterColumn As String = "", Optional filterValue As String = "", Optional secondaryFilterColumn As String = "", Optional secondaryFilterValue1 As String = "", Optional secondaryFilterValue2 As String = "", Optional addSequence As Boolean = False) As Boolean

This is the data in the columns that belong to the filters.

10081200
2SG02011	28.58
2US00080
2FR08021	6.08
10081200
10081200

The variants that require a filter are

GenerateFile ws, columns2, lastRow, filePath1 & "UPU41\_Desc\_" & Format(Date, "yyyy-mm-dd") & ".xlsx", "H", "2\*" 

GenerateFile ws, columns3, lastRow, filePath1 & "UPU41\_Cond\_" & Format(Date, "yyyy-mm-dd") & ".xlsx", "H", "2\*", "AK", "6.08", "28.58" 

GenerateFile ws, columns5, lastRow, filePath1 & "UPU43\_Desc\_" & Format(Date, "yyyy-mm-dd") & ".xlsx", "H", "1\*", , , , True 

GenerateFile ws, columns6, lastRow, filePath1 & "UPU43\_Cond\_" & Format(Date, "yyyy-mm-dd") & ".xlsx", "H", "1\*", , , , True

When columns2 runs, no filter is applied. Here are the results. We should only be seeing rows from the original file where the LIFNR column starts with a 2.

WERKS	MATNR	LIFNR
	MH62D9VWP	10081200
	MH68D9VWP	2SG02011
	MH74D9VWP	2US00080
	MH80D9VWP	2FR08021
	MH86D9VWP	10081200
	MH92D9VWP	10081200

When columns3 runs, the first filter works, but the secondary does not. The Results here do show LIFNR with a 2 but the KBETR column should not have a blank field.

LIFNR	EKORG	KSCHL	KBETR
2SG02011			28.58
2US00080
2FR08021			6.08

For columns5 and columns6 variants, the results are the same from the columns2 variant, but in this case, they should only show the LIFNR that begin with a 1.

YSRNO	MATNR	LIFNR
1		MH62D9VWP
2		MH68D9VWP
3		MH74D9VWP
4		MH80D9VWP
5		MH86D9VWP
6		MH92D9VWP

Here is the output from the Debug on the columns2 and columns3 variants.

Row: 5 - FilterColumn: H - Value: 10081200 - Matches: False

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 5 matches filter criteria.

Row: 6 - FilterColumn: H - Value: 2SG02011 - Matches: True

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 6 matches filter criteria.

Row: 7 - FilterColumn: H - Value: 2US00080 - Matches: True

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 7 matches filter criteria.

Row: 8 - FilterColumn: H - Value: 2FR08021 - Matches: True

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 8 matches filter criteria.

Row: 9 - FilterColumn: H - Value: 10081200 - Matches: False

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 9 matches filter criteria.

Row: 10 - FilterColumn: H - Value: 10081200 - Matches: False

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 10 matches filter criteria.

Applying sensitivity label to: C:\Users\Desktop\UPU41_Desc_2025-01-02.xlsx

Sensitivity label applied successfully.

Saving file: C:\Users\Desktop\UPU41_Desc_2025-01-02.xlsx

Generating file: C:\Users\Desktop\UPU41_Cond_2025-01-02.xlsx

Row: 5 - FilterColumn: H - Value: 10081200 - Matches: False

Row: 5 - SecondaryFilterColumn: AK - Value:  - Matches: 29

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 5 does not match filter criteria.

Row: 6 - FilterColumn: H - Value: 2SG02011 - Matches: True

Row: 6 - SecondaryFilterColumn: AK - Value: 28.58 - Matches: 29

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 6 matches filter criteria.

Row: 7 - FilterColumn: H - Value: 2US00080 - Matches: True

Row: 7 - SecondaryFilterColumn: AK - Value:  - Matches: 29

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 7 matches filter criteria.

Row: 8 - FilterColumn: H - Value: 2FR08021 - Matches: True

Row: 8 - SecondaryFilterColumn: AK - Value: 6.08 - Matches: -1

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 8 matches filter criteria.

Row: 9 - FilterColumn: H - Value: 10081200 - Matches: False

Row: 9 - SecondaryFilterColumn: AK - Value:  - Matches: 29

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 9 does not match filter criteria.

Row: 10 - FilterColumn: H - Value: 10081200 - Matches: False

Row: 10 - SecondaryFilterColumn: AK - Value:  - Matches: 29

FilterValue: 2*

SecondaryFilterValue1: 6.08 - SecondaryFilterValue2: 28.58

Row 10 does not match filter criteria.

Applying sensitivity label to: C:\Users\Desktop\UPU41_Cond_2025-01-02.xlsx

Sensitivity label applied successfully.

Saving file: C:\Users\Desktop\UPU41_Cond_2025-01-02.xlsx

To me, it appears that the values that are entered into the variants for the filters are not being read properly. Looking at Row:5 from columns2, it shows the value of 100* and the match is false, but then the last line shows Row 5 matches filter criteria so everything is copied over.

Row: 5 - FilterColumn: H - Value: 10081200 - Matches: False

FilterValue: 2*

SecondaryFilterValue1:  - SecondaryFilterValue2:

Row 5 matches filter criteria.

The reason this shows row 5 but you see row 1 above in the data, the file starts to copy from row 5.

I hope this helps a little with trying to figure out what is wrong.

Thank you

Answer 4

Hi Snow Lu, There is a Word document on my original message as well that has a very long and detailed explanation. If you could please download that, I think you will find all the information there as well as what the expect results I am looking for.

Thank you for taking the time to look at this.

Answer 5

From your description, it seems too many questions in your word file.

I have downloaded the excel workbook. We can work together to make it work one by one.

Could you share which line you want to fix first and show expected result with your test file? Then I can have a try on correcting the code.

Please understand that initial reply may not always immediately resolve the issue due to limited information. However, with your help and more detailed information, we can work together to find a solution.