Excel/VBA row delete
I have another VBA question please, I thought I had it covered but the bit of VBA code I have is extremely slow. I have never used it on such a large file before (approx. 900,000 rows), or deleted 99 rows at a time,usually only a couple of rows, there has to be a better way to do what I am after. Here is the code I am trying to use:
Dim lastRow As Long, rowCounter As Long
Dim InterVal As Integer, StartRow As Integer, NoRow As Integer
Application.ScreenUpdating = False
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
InterVal = 1 'miss
Start = 9
NoRows = 99 'Delete
rowCounter = Start
Do While rowCounter <= lastRow
ActiveSheet.Rows(rowCounter & ":" & rowCounter + NoRows - 1).Delete
lastRow = lastRow - NoRows
rowCounter = rowCounter + InterVal
Basically, I am wanting to thin the data out. I start at row 9 and then delete 99 rows, miss 1 row, delete 99 row, miss 1 row and so on until I reach the end of the data. Something like this – If I had numbers like 1 2 3.... to 100 and 101 102 103.... to 200 and 201 202 203 .... to 300 and so on all the way to approx 900,000, I would only like to keep something like 1, 100, 200, 300 and so on.
There are approximately 900,000 rows of data.
Any help or improvements you can offer is greatly appreciated.
I am using Excel 2016.
Possibly you have formulas in your data. If you do, then turn calculation to manual.
If you have use print preview or printed your data and the pagebreaks are visible then you need to turn off pagebreaks before running your code.
Personally I wouldn't use a macro. You could use a formula in a column to the right or insert a new column A.
A1 or A9: =if(mod(row(),100)=0,"Keep","Delete")
then select the cell with the formula and look at the lower right corner of the highlight. Double click the little square on the lower right and the formula should fill down next to your data.
then I would copy that column and do a paste values.
Now you can filter on that column and choose delete
Select all the rows and hit the delete key.
or you could use a formula like
and follow the above steps but instead of filtering, you can sort.
or you could use the first formula and filter on Keep, then select all your data and copy it to another sheet. Only the visible rows should be copied.
None of the methods should be instantaneous, but your dealing with a lot of data.