D
Dennis
Using XL 2003
Apparently, I am too close to the problem to figure out an error in the following routine.
Sub DeleteBlankRows() is to delete all Rows which are empty in column H.
In addition, there is a routine that I obtained from Ron De Bruin to workaround the 8192
noncontigious-cell limitation in XL. Ron's code works fine!
The problem is some rows whose cells are not blank in column H, are being deleted.
Interestingly, those same unintentionally deleted rows - ARE blank in columns A through G;
but not in column H!
Therefore, when I merged my delete rows routine with the 8192 workaround, I must
have accidently changed my reference column to base the selection for deletion.
[From column H to ????]
Can anyone see my error?
TIA Dennis
Sub DeleteBlankRows()
'
'
' Procedure to filter for blanks in Column H then delete those same rows in range (H:H2)
'
Dim DeleteValue As String
Dim myDeleteRowRange As Range
Dim Counter As Long
Counter = Cells.SpecialCells(xlCellTypeLastCell).Row
DeleteValue = ""
With ActiveSheet _
.Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row).AutoFilter Field:=1, _
Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set myDeleteRowRange = .Offset(1, 0).Resize(.Rows.Count - 1,1) _
.SpecialCells(xlCellTypeBlanks) 'Counts Rows to delete
If myDeleteRowRange Is Nothing _
Then MsgBox "NOTE: Empty Rows were not located!"
For Counter = Counter To 1 Step -8000
Range(Cells(Application.WorksheetFunction.Max(1, Counter - 7999), 1), _
Cells(Application.WorksheetFunction.Max(Counter, 1), 1)). _
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next Counter
On Error GoTo 0
End With
.AutoFilterMode = False
.UsedRange 'Resets UsedRange
End With
End Sub
Apparently, I am too close to the problem to figure out an error in the following routine.
Sub DeleteBlankRows() is to delete all Rows which are empty in column H.
In addition, there is a routine that I obtained from Ron De Bruin to workaround the 8192
noncontigious-cell limitation in XL. Ron's code works fine!
The problem is some rows whose cells are not blank in column H, are being deleted.
Interestingly, those same unintentionally deleted rows - ARE blank in columns A through G;
but not in column H!
Therefore, when I merged my delete rows routine with the 8192 workaround, I must
have accidently changed my reference column to base the selection for deletion.
[From column H to ????]
Can anyone see my error?
TIA Dennis
Sub DeleteBlankRows()
'
'
' Procedure to filter for blanks in Column H then delete those same rows in range (H:H2)
'
Dim DeleteValue As String
Dim myDeleteRowRange As Range
Dim Counter As Long
Counter = Cells.SpecialCells(xlCellTypeLastCell).Row
DeleteValue = ""
With ActiveSheet _
.Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row).AutoFilter Field:=1, _
Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set myDeleteRowRange = .Offset(1, 0).Resize(.Rows.Count - 1,1) _
.SpecialCells(xlCellTypeBlanks) 'Counts Rows to delete
If myDeleteRowRange Is Nothing _
Then MsgBox "NOTE: Empty Rows were not located!"
For Counter = Counter To 1 Step -8000
Range(Cells(Application.WorksheetFunction.Max(1, Counter - 7999), 1), _
Cells(Application.WorksheetFunction.Max(Counter, 1), 1)). _
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next Counter
On Error GoTo 0
End With
.AutoFilterMode = False
.UsedRange 'Resets UsedRange
End With
End Sub