We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Delete All Visible (Unfiltered) Rows


I am trying to determine how to delete all of the visible (unfiltered) rows using Xlsio). Using Interop, the code would be:

        Worksheet.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(XlCellType.xlCellTypeVisible).EntireRow.Delete()

Afterwards I unfilter and am only left with all of the rows previously filtered for further processing.

Is there an equivalent in Xlsio? So far I have not had luck mimicing this behavior outside of loops which are very slow for the thousands of rows I have to process.


5 Replies

DB Dilli Babu Nandha Gopal Syncfusion Team February 18, 2019 11:23 AM UTC

Hi Curtis, 

Greetings from Syncfusion. 

We have prepared a sample to delete all visible (unfiltered) rows which can be download from the following link. 

Please try this and let us know if this fulfills your requirement. 

Dilli babu. 

CU Curtis February 18, 2019 09:18 PM UTC

Thank you,

I am currently converting an application from Vb.Net Interop to the XLSIO language and this is an area that, in interop, is 1 line of code and occurs nearly instantly. The code below took well over 4 minutes and by then it was apparent it would not work. I converted this into VB.net (from C#). Is my code wrong? There are 60 thousand lines it has to go through, so quite a long time.

            Worksheet.AutoFilters.FilterRange = Worksheet.UsedRange
            Dim filter As IAutoFilter = Worksheet.AutoFilters(FilterColumn)
            filter.AddTextFilter(New String() {"Fruits"})
            Dim Range = Worksheet.AutoFilters.FilterRange
            Dim row As Integer = Range.Row
            Dim lastRow As Integer = Range.LastRow

            For i As Integer = row + 1 To lastRow
                Dim storange As RowStorage = WorksheetHelper.GetOrCreateRow((TryCast(Worksheet, IInternalWorksheet)), i - 1, True)

                If Not storange.IsHidden Then
                    i -= 1
                    lastRow -= 1
                End If

            For j As Integer = 0 To Worksheet.AutoFilters.Count - 1
                Dim filterImpl As AutoFilterImpl = TryCast(Worksheet.AutoFilters(j), AutoFilterImpl)

AV Abirami Varadharajan Syncfusion Team February 19, 2019 11:29 AM UTC

Hi Curtis,  

We have prepared VB sample to achieve your requirement and the sample can be downloaded from following link. 

Kindly try this and let us know that your requirement is fulfilled. 


GB Gavin Blem June 10, 2019 09:52 AM UTC

Hi Dilli and Abirami

The methods you propose above are both far too slow i.e. your loops take many minutes to delete rows from anything but the smallest XLS, so I'm sure do not meet Curtis's requirement.

Is there a faster method or can DeleteRow() have an IsFiltered parameter or similar, and it take just a second or less to delete all the rows visible after filtering?


AV Abirami Varadharajan Syncfusion Team June 11, 2019 01:52 PM UTC

Hi Gavin, 

Greetings from Syncfusion. 

We request you to share us below details to analyse the performance issue from our side. 

  1. Total no of rows and columns used in the worksheet.
  2. Total no of rows visible after filter in the worksheet.
It would be helpful if you share us the Excel document which you have facing the performance issue to provide the prompt solution at earliest. 


Live Chat Icon For mobile
Up arrow icon