VBA help (Empty row delete; coupled with 8192 non-contigious issue]

[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

 

[Ron de Bruin]

Hi Dennis

David have a example om his site for you
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()

 

[Ron de Bruin]

If you want to change the column Dennis.

David use the Cells function two times to create the range
Cells(row,column)

You must change the number after the , to the column number you want
1 = Column A
8 = Column H

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

Hi Dennis

David have a example om his site for you
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()



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

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

 

[Dennis]

Hi Dennis

David have a example om his site for you
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()

Hello Ron!

The "8192" routine itself is exactly what I am using and is OK by itself

That said, in that routine, how do I change the column to evaluate the empty cells
from (I think) "A" to column " H"?

TIA Dennis

 

[Dennis]

If you want to change the column Dennis.

David use the Cells function two times to create the range
Cells(row,column)

You must change the number after the , to the column number you want
1 = Column A
8 = Column H

Our messages crossed! Thanks!

 

[Ron de Bruin]

See my other reply Dennis in this thread