You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- VBA row delete

Advertisement

Hi tom,

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:

Sub DeleteRows()

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

Loop

End Sub

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.

Regards

Natalie

Natalie,

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

=if(mod(row(),100)=0,"aKeep","bDelete")

and follow the above steps but instead of filtering, you can sort.

then delete

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.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thank you Tom, I will give this a go. Cheers Natalie |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.