MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have a spreadsheet within 900000 lines, it is Excel 2007 and it is a dump from SAP.
problem is that a large number of rows are junk and need to be deleted.
I have added a marker to every line that needs to be deleted, but the computer can't cope with a sort, i have written a macro that works its way through the file deleted every line, but it takes a long time.
It did dawn on me on the way home that the marker is an if statement. Would a copy paste to turn this into a value and not a formula speed things up much? Any other ideas of how to sort this in a way that will take less than a month!
IMPORT TO ms aCCESS, DELETE, EXPORT TO eXCEL
Without caps lock on
hmm if excel won't sort try sticking in access?
was thinking the access route, just curious if there were any ways within excel. Will have to hope my new employer has put a copy of access on my PC.
could try splitting it into say 4 worksheets, see if that helps sorting, then stick back together?
Can you filter on the column with the marker the delete the filtered rows?
If you must use Excel, set calculations to manual and set application.screenupdating (or something like this) to false.
900,000 rows is a pretty unmanageable amount, but if you really want to do it in Excel the best way is a VBA procedure that creates an array of all the rows to be deleted and removes them all at the same time - much more efficient than individually.
Just so happens I've got a script written I can modify if you let me know some details e.g. what the marker is & where it appears.
thanks, the marker is wibble and is in column 8.
Give me a mo...
Here you go mrmo. Includes the option to add in other markers - NB these are case sensitive.
**********************************
Sub Remove_Unwanted_Rows()
Dim Cell As Object
Dim LastCell As Object
Dim DelRange As Range
Dim RowArray As Range
Set LastCell = Range("H1048576")
Set DelRange = Range(LastCell.End(xlUp), LastCell.End(xlUp).End(xlUp))
For Each Cell In DelRange
Select Case Cell.Value
Case Is = "wibble" ', "wubble", "wobble"
If RowArray Is Nothing Then
Set RowArray = Cell.EntireRow
Else
Set RowArray = Union(RowArray, Cell.EntireRow)
End If
End Select
Next Cell
RowArray.Delete
End Sub
**********************************
Hmmm... indenting removed itself - oh well