|
Howcan I delete blank rows efficiently
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 |
Howcan I delete blank rows efficiently
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 |
Howcan I delete blank rows efficiently
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 |
Howcan I delete blank rows efficiently
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 " |
Howcan I delete blank rows efficiently
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 |
Howcan I delete blank rows efficiently
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 |
| All times are GMT +1. The time now is 11:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com