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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Delete All Visible (Unfiltered) Rows

Thread ID:

Created:

Updated:

Platform:

Replies:

142737 Feb 17,2019 08:47 PM UTC Jun 11,2019 01:52 PM UTC WinForms 5
loading
Tags: XlsIO
Curtis
Asked On February 17, 2019 08:47 PM UTC

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

Dilli Babu Nandha Gopal [Syncfusion]
Replied On 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. 

Regards, 
Dilli babu. 


Curtis
Replied On 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
                    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

Abirami Varadharajan [Syncfusion]
Replied On 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. 

Regards, 
Abirami 


Gavin Blem
Replied On 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?

Regards
Gavin

Abirami Varadharajan [Syncfusion]
Replied On 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. 

Regards, 
Abirami 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon