Applying Auto-filter on excel sheet through VB.net

Question

I was wondering where I am going wrong, I basically want to apply an auto filter on an excel sheet i'm opening through vb.net and then read through the filtered data.

I have read almost every vb/autofilter related answer but I must be doing something terribly wrong but just can seem to spot it!

xlWorkSheet = xlWorkBook.Sheets(2)
            xlWorkSheet.Name = "ACC"

            xlWorkSheet.Range("$A$4:$AE$4480").AutoFilter(Field:=31, Criteria1:="=Accepted")

            xlCell = xlWorkSheet.UsedRange
            intLstRowIdx = xlWorkSheet.Range("A" & xlWorkSheet.Rows.Count).End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row

            For rcnt = intLstRowIdx To 2 Step -1

                Dim Obj = CType(xlCell.Cells(rcnt, 31), excel.Range)

                If Obj.Text.ToString.ToUpper.Contains("ACC") = True Then

                    xlWorkSheet.Rows(rcnt).Delete()

                End If

            Next

What i would expect this to do would be to apply the filter to the excel sheet and then basically mkae all my records/rows be ones in which the column 31 has "Accepted" in it, therefore i would attempt the change the delete row part to a much quicker delete such as delete range or that etc.

But when i run the above code it runs and all but it doesn't seem to keep or even run the filter as "Rejected" records are still showing up in my for loop etc.

I appreciate any help guys, I really have tried loads and just know it is me being dumb and missing out on a single line or contradicting myself within my code.

edit I have used excel to record the macro of me performing the filter in excel and the line xlWorkSheet.Range("$A$4:$AE$4480").AutoFilter(Field:=31, Criteria1:="=Accepted") is what I was given.

JohnyL · Accepted Answer · 2019-06-12T11:43:06.663

2

The fast method is to use SpecialCells to get all the filtered range. After getting it you can delete all rows in one line:

Dim rngTable, rngData, rngVisible As Excel.Range

'// Original data
rngTable = xlWorkSheet.Range("$A$4:$AE$4480")
'// Exclude header - we don't want it to take a part in filtering
With rngTable
    '// Offset range by one row and then resize it excluding last row.
    '// This way we obtain all cells without header.
    rngData = rngTable.Offset(1).Resize(.Rows.Count - 1)
End With

'// Filtering
rngData.AutoFilter(Field:=31, Criteria1:="=Accepted")

Try
    '// SpecialCells takes all visible cells (i.e. filtered), which is what we need.
    '// We need to use it in Try..Catch because if there are no filtered cells,
    '// this method will throw exception.
    rngVisible = rngData.SpecialCells(Excel.XlCellType.xlCellTypeVisible)
    '// Having obtained all cells, delete entire rows.
    rngVisible.EntireRow.Delete()
Catch ex As Exception
    '// We're here 'cause no rows were filtered
End Try

edited Jun 12 '19 at 11:43

answered Jun 12 '19 at 08:51

JohnyL

6,894
3
22
41

Do you mind giving a simple explanation of what each of the lines is doing? Particularly how its bulk deleting? – K.Madden Jun 12 '19 at 11:16
1

@K.Madden I have added explanations – JohnyL Jun 12 '19 at 11:43
Was it because MODS don't like us saying thank you and welcome in comments? – K.Madden Jun 12 '19 at 12:16
@K.Madden Perhaps, I missed such "feature") I see this for the first time... – JohnyL Jun 12 '19 at 12:19
1

@K.Madden So, did explanation help you? :) – JohnyL Jun 12 '19 at 12:20
Yes it was exactly what I needed! Thank you again! Hopefully they don't delete this comment! – K.Madden Jun 12 '19 at 12:29
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194828/discussion-between-johnyl-and-k-madden). – JohnyL Jun 12 '19 at 12:41

Applying Auto-filter on excel sheet through VB.net

1 Answers1

Linked