Fast way of deleting a lot of rows

C

citroendealer

New Member
Joined
Sep 27, 2016
Messages
13
Hello fellow VBA'ers,

For a project I need to make a data set more readable. For this I need to delete 18 rows and then skip 1. This needs to be done as many elements I have in my data set. The code that is use is shown below. j is the number of elements and is prescribed within my code. The problem is that i have sometimes 2000 or 5000 elements. This takes minutes to run this part of the code. Is there a faster way?


Code: 
            For i = 1 To j
                For a = 1 To 18
                    ActiveCell.EntireRow.Delete
                Next a
                Selection.Offset(1, 0).Select
            Next i
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi citroendealer,

Are the rows that you are skipping blank rows or is there a certain cell in each row that is the same (either blank or the same value) that can be used to identify a row that has to be deleted...

igold
 
Upvote 0
Dear igold,

No the rows are not empty, see picture below. For each region he makes a new table. With this macro i want to delete all the in between information so the result will be one table with all the element numbers beneath each other.

d2kjh7vjd
 
Upvote 0
See if this does what you want:

Code: 
Sub DeleteAllBut19thRow()
  Dim a, b
  Dim nc As Long, i As Long
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If i Mod 19 <> 0 Then b(i, 1) = 1
  Next i
  Application.ScreenUpdating = False
  With Range("A1").Resize(UBound(a), nc)
    .Columns(nc).Value = b
    .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    On Error Resume Next
    .Columns(nc).SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

(adapted from http://www.mrexcel.com/forum/excel-questions/935646-faster-way-delete-rows-0-value.html)

It deletes all but every 19th row very quickly. Data starts in A1.
 
Last edited:
Upvote 0
Sorry but when i start this macro the result is a empty workbook. True this link you can download a small version of my excel file. It is a Dropbox link I don't know of this is allowed.
 
Upvote 0
From your link... do you want to keep rows 20,21,23,39,40,42,58,59,61 etc...
 
Upvote 0
I want to keep the first rows 1 up to and including 22, then I want to keep every 19th row starting with 23 then 42, 61, 80 etc.
 
Upvote 0
He basically said he wants to keep rows that are multiples of 19 in his original post.
 
Upvote 0
Hi,

Does this do what you want...

Code: 
Sub Delete19()


Dim lrow As Long


lrow = Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("B22", "B" & lrow).SpecialCells(2, 1).EntireRow.Delete


End Sub

igold
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Select next row at column A after row has been deleted
Replies
4
Views
63
ipbr21054
I
B
  • Question
Add new Row in ListObject Table with data from an Array
Replies
4
Views
63
Akuini
Akuini
Z
  • Question
Selenium. Is there a way to stop loading a web page in Chrome?
Replies
0
Views
329
zgthgt
Z
Romano_odK
  • Solved
Is there something faster to erase lines?
2
Replies
19
Views
628
FormR
FormR
E
  • Solved
Summing Values
Replies
3
Views
204
LazyBug
LazyBug

Forum statistics

Threads
1,214,626
Messages
6,120,602
Members
448,974
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top