Question from VB novice...

Can someone suggest how to make the follwoing macro more efficient ?

It will process 40K plus rows and is very slow.

I've tried adjusting the chunk processing size from, 100 to 5000. Smaller
chunk appears to be faster.

All suggestions welcome...

thanks in advance...


Dim Rng As Range
Dim RawDataRowNdx As Long
Dim LastRowOfRawData As Long
' Turn Screen Updateing and sheet Calculation OFF to go faster
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
' Set sheet to Raw Data Area sheet
'
Sheets("Raw Data Area").Select
'
' Find Last Row of Data
LastRowOfRawData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


'
' Delete All Blank Rows in 100 row chunks
'
For RawDataRowNdx = 100 To LastRowOfRawData Step 100
Range("N1:N" & RawDataRowNdx).SpecialCells(xlBlanks).EntireRow.De lete
Application.StatusBar = RawDataRowNdx & "K Records Processed... Still
Working!!!"
Next RawDataRowNdx

Hi

You can sort first and delete it in one step
EasyFilter have a optionn to do this also
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"needyourhelp" wrote in message ...
Question from VB novice...

Can someone suggest how to make the follwoing macro more efficient ?

It will process 40K plus rows and is very slow.

I've tried adjusting the chunk processing size from, 100 to 5000. Smaller
chunk appears to be faster.

All suggestions welcome...

thanks in advance...


Dim Rng As Range
Dim RawDataRowNdx As Long
Dim LastRowOfRawData As Long
' Turn Screen Updateing and sheet Calculation OFF to go faster
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
' Set sheet to Raw Data Area sheet
'
Sheets("Raw Data Area").Select
'
' Find Last Row of Data
LastRowOfRawData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


'
' Delete All Blank Rows in 100 row chunks
'
For RawDataRowNdx = 100 To LastRowOfRawData Step 100
Range("N1:N" & RawDataRowNdx).SpecialCells(xlBlanks).EntireRow.De lete
Application.StatusBar = RawDataRowNdx & "K Records Processed... Still
Working!!!"
Next RawDataRowNdx

The following code assumes that Column "O" is empty and not used
and that data bewteen columns A-N is to be deleted, if not just alter
the code to suit.

Like Ron's suggestion, it sorts the data, remove blank rows and then
sort the data back in its original order (less blanks)



Dim Rng As Range
Dim LastRowOfRawData As Long
' Turn Screen Updateing and sheet Calculation OFF to go faster
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
' Set sheet to Raw Data Area sheet
'
Sheets("Raw Data Area").Select
'
' Find Last Row of Data
LastRowOfRawData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


Range("O" & LastRowOfRawData).Select
ActiveCell.Value = Selection.Row
ActiveCell.Offset(-1, 0).Value = Selection.Row - 1
Range(ActiveCell.Offset(-1, 0), ActiveCell).Select


Selection.AutoFill Destination:=Range("O1", ActiveCell.Offset(1,
0)), Type:=xlFillDefault
Columns("A:O").Select
Selection.Sort Key1:=Range("N1"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("N1").Select
Selection.End(xlDown).Select

Selection.Offset(1, 1).Select
Range("A" & ActiveCell.Row, ActiveCell.End(xlDown)).Select
Selection.ClearContents

Columns("A:O").Select
Selection.Sort Key1:=Range("O1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("O:O").Select
Selection.ClearContents

Range("A1").Select

HTH

Tony

Sorry, just re-read my own post and should have said :

"The following code assumes that Column "O" ........ and that data
bewteen AND INCLUDING columns A-N is to be deleted, etc etc "

Ron,

I downloaded easyfilter it works great...

Now, How do you call it from a VBA macro so it is automated ?

thanks,

Tim



"Ron de Bruin" wrote:

Hi

You can sort first and delete it in one step
EasyFilter have a optionn to do this also
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"needyourhelp" wrote in message ...
Question from VB novice...

Can someone suggest how to make the follwoing macro more efficient ?

It will process 40K plus rows and is very slow.

I've tried adjusting the chunk processing size from, 100 to 5000. Smaller
chunk appears to be faster.

All suggestions welcome...

thanks in advance...


Dim Rng As Range
Dim RawDataRowNdx As Long
Dim LastRowOfRawData As Long
' Turn Screen Updateing and sheet Calculation OFF to go faster
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
' Set sheet to Raw Data Area sheet
'
Sheets("Raw Data Area").Select
'
' Find Last Row of Data
LastRowOfRawData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


'
' Delete All Blank Rows in 100 row chunks
'
For RawDataRowNdx = 100 To LastRowOfRawData Step 100
Range("N1:N" & RawDataRowNdx).SpecialCells(xlBlanks).EntireRow.De lete
Application.StatusBar = RawDataRowNdx & "K Records Processed... Still
Working!!!"
Next RawDataRowNdx

You can't access EasyFiler with code
Start with your macro recorder and record the things you want.


--
Regards Ron de Bruin
http://www.rondebruin.nl



"needyourhelp" wrote in message ...
Ron,

I downloaded easyfilter it works great...

Now, How do you call it from a VBA macro so it is automated ?

thanks,

Tim



"Ron de Bruin" wrote:

Hi

You can sort first and delete it in one step
EasyFilter have a optionn to do this also
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"needyourhelp" wrote in message
...
Question from VB novice...

Can someone suggest how to make the follwoing macro more efficient ?

It will process 40K plus rows and is very slow.

I've tried adjusting the chunk processing size from, 100 to 5000. Smaller
chunk appears to be faster.

All suggestions welcome...

thanks in advance...


Dim Rng As Range
Dim RawDataRowNdx As Long
Dim LastRowOfRawData As Long
' Turn Screen Updateing and sheet Calculation OFF to go faster
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
' Set sheet to Raw Data Area sheet
'
Sheets("Raw Data Area").Select
'
' Find Last Row of Data
LastRowOfRawData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


'
' Delete All Blank Rows in 100 row chunks
'
For RawDataRowNdx = 100 To LastRowOfRawData Step 100
Range("N1:N" & RawDataRowNdx).SpecialCells(xlBlanks).EntireRow.De lete
Application.StatusBar = RawDataRowNdx & "K Records Processed... Still
Working!!!"
Next RawDataRowNdx