allison
12-29-2008, 08:42 AM
I'm trying to use this code from the knowledge base:
Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Goto Exits:
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw
Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
However, it doesn't seem to be working. The database that I'm using has slightly over 300K lines, of which every other line is blank. I don't know if there is something wrong with my computer as Excel goes "non responding" and I have to use the task manager to shut it down. When I reopen the spreadsheet, no more lines have been deleted that previously, which is why it doesn't seem to be working.
If anyone has some suggestions, I certianly would welcome anything!
Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Goto Exits:
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw
Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
However, it doesn't seem to be working. The database that I'm using has slightly over 300K lines, of which every other line is blank. I don't know if there is something wrong with my computer as Excel goes "non responding" and I have to use the task manager to shut it down. When I reopen the spreadsheet, no more lines have been deleted that previously, which is why it doesn't seem to be working.
If anyone has some suggestions, I certianly would welcome anything!