You are here:

Excel/Delete rows on large file

Advertisement


Question
QUESTION: Hi Gulshan,

I have a VBA question please, I thought I had it covered but the bit of VBA code I have is very very slow. I have never used it on such a large file before and 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. There are approximately 900,000 rows of data.

Any help or improvements you can offer is greatly appreciated.

Regards
Natalie

ANSWER: Dear Natalie,

I did a test run of your code and you are effectively deleting all the rows. If that is the case, why would you want to delete them 99 rows at a time, why not delete all of them at one go?
Is there any specific purpose of deleting 99 rows at a time?
Depending on your answer to the above, I can try to make the necessary corrections to the code.

Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Hi Gulshan,

Thanks for taking a look at the code.

The code is meant to start at row 9 and delete 99 rows and then miss 1 row and so on. If I had a 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 1, 100, 200, 300 and so on. I just want to thin the data out.

Thanks again for your time and help.

Natalie

ANSWER: Dear Natalie,

I tried a few other macro approaches, but the performance is still slow.

I've done more research and looks like any macro where the number of rows to be deleted is more than 1000 rows will cause your system to slow down and hang. In your case, its 900000 rows, which is much higher.

I have one alternative suggestion, instead of trying to delete the 99 rows, why don't you take every 100th row and filter them or copy them to a new sheet and work with them.

That way, you have to handle only about 9000 rows instead of the current approx 890000 rows.

Please let me know if this is fine and I can find a macro to do that job for you.


Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Thanks Gulshan,

I would appreciate you trying your way, it sounds like a better approach then my way.

Regards
Natalie

Answer
Dear Natalie,

Please try using the below code. This will first identify every 100th record with a different color, then filter those identified records and copy them to another sheet (Sheet2).

Please see if this serves your purpose.

Sub pk99rws()

Application.ScreenUpdating = False
rcnt = WorksheetFunction.CountA(Range("A:A"))
actcll = 9
Do While actcll <= rcnt
Range("A" & actcll).Select
With Selection.Interior
       .Color = 65535
End With
actcll = actcll + 100
Loop
Range("A:A").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A1")


End Sub

Hope this helps.
Thanks,
Gulshan.
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.