Delete All Visible (Unfiltered) Rows

[Curtis]

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]

Hi Curtis, 

Greetings from Syncfusion. 

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

Sample link : http://www.syncfusion.com/downloads/support/forum/142737/ze/DeleteAllVisibleRow-1051040956.zip  

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

Regards, 

Dilli babu. 

[Curtis]

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]

Hi Curtis,  

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

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Filter-668190459.zip  

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

Regards, 

Abirami 

[Gavin Blem]

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]

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