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

Delete All Visible (Unfiltered) Rows

Hello,

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.

-Curtis

5 Replies

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

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. 

Regards, 
Dilli babu. 



CU Curtis February 18, 2019 04:18 PM

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
                    Worksheet.DeleteRow(i)
                    i -= 1
                    lastRow -= 1
                End If
            Next

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


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

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. 

Regards, 
Abirami 



GB Gavin Blem June 10, 2019 05:52 AM

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?

Regards
Gavin


AV Abirami Varadharajan Syncfusion Team June 11, 2019 09:52 AM

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. 

Regards, 
Abirami 


Loader.
Live Chat Icon For mobile
Up arrow icon