Delete a lot of rows

Help Activities
, ,
1

Guys, Hello.

I need to delete a lot of rows (about 6000 sometimes).

If I you Delete Rows, it’s too slow.
I deleted rows by Invoke code, but it still slow.

I know about Delete Range, it’s not the best choice, because it doesn’t save heights of rows.
(I try to do all without autoheight).

Do you know how to do it otherwise?

2

So you used for each and vba, but both are slow for you?

How come vba is slow?

Is it possible to share the code? that will give is the idea about your query and what condition you are using to delete rows.

3

I failed a bit. Now it’s BC30574 Strict on error in line wb.Worksheets(“Sheet1”).Rows(0).Delete().
I didn’t try to do it using query, maybe it’will become faster. Thank you a lot for the idea!

Invoke VB.NET code:

Dim exc As Application
Dim wb As Workbook

exc = New ApplicationClass

wb = exc.Workbooks.Open(“C:\Users\young\Desktop\Test.xlsx”)

For i As Int32 = 1 To 10
wb.Worksheets(“Sheet1”).Rows(0).Delete()
Next

wb.Save()
wb.Close()

4

Hi

So how many row you want to delete and on what condition you would like to delete

@YoungFave

5

I delete by indexes.
It’s not a problem.
Long lasting is problem.

6

If the number of rows to delete is high then instead of deleting we can get the rows we want
Like in the other way

@YoungFave

7

That’s cool idea, how I can do it?

8

I have about 10 different letters on the same worksheet.
I need to know how many pages it will be If I print it. (do it using PageSetup.Pages.Count)

9

That’s why I would like to know the condition based on which you are deleting

We can either use FILTER DATATABLE to get the one we need

@YoungFave

10

Filter isn’t solution.
I want to delete first 1000 rows, for example.
Or filter can do it?

1 Like
11

In that case

if you have your datatable in dt, use a assign activity like this

dt = dr.AsEnumerable().Skip(skipRows).CopyToDataTable

Skip - Will skip the unwanted rows

This would delete the one you don’t need

Cheers @RPA-botDev @YoungFave

12

What’s the (skiprow) description

1 Like
13

lets assume we do have a datatable with 4589 rows and want to delete / skip the first 1000 rows:

yourDataTableVar.AsEnumerable.Skip(1000).CopytoDataTable

will return a datatable with the without the origin first 1000 rows (3589 rows)

When we want to make it more flexible we just use a variable: skipCount and can set it as we want to use:

skipCount = 1000
yourDataTableVar.AsEnumerable.Skip(skipCount).CopytoDataTable

2 Likes
14

Fine

Usually there are two options we can use

Either we use SKIP or TAKE
We call it as Clause in vb net

For example
If you want to skip rows from the top day 100 rows then mention that with SKIP clause

dt = dt.AsEnumerable().Skip(100).CopyToDataTable

Suppose if you want to have only first 100 rows then use TAKE clause like this

dt = dt.AsEnumerable().Take(100).CopyToDataTable

As simple as that

For more details technically

Cheers @RPA-botDev @YoungFave

1 Like
15

Thanks @Palaniyappan and @ppr

16

Glad @RPA-botDev

Do we have any further queries on this topic @YoungFave

17

Hi Palaniyappan, pls how do I remove white space from datatable cause I am confused. .Trim doesn’t work for datatable

18

From a datatable or from a column in datatable

Cheers @RPA-botDev

19

From a column please

20

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.