Solved: A smarter way to delete rows with blanks?

[Simon Lloyd]

Hi all, i answered a post the other day where the Op wanted to delete any row that had a blank in it, so this is how i proposed it should be done:

Sub Del_Rows_Containing_Blanks()
Dim ColCnt As Integer
Dim Rng As Integer
Dim Rng1 As Range
Rng = Range("A65536").End(xlUp).Row
ColCnt = Cells(1, Columns.Count).End(xlToLeft).Column
Set Rng1 = Range(Cells(1, 1), Cells(Rng, ColCnt))
With Rng1
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
Anyone have a simpler or smarter way?

[jammer6_9]

Might not smarter but I use this code and gives me what I want.


Sub DeleteEmptyRowsInSheet()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 2 Step -1
If UCase(Cells(r, 1).Value) = "" Then
With Rows(r)
.Delete

End With
End If
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[Simon Lloyd]

Jammer, the code you are using there will only delete blank rows not rows that contain blanks.

[tstav]

delete any row that has a blank in it
Hi Simon,
1)By using xlUp and xlToLeft it is very probable that you will miss rows or columns in case the data is ragged. I mean the Range you'll be selecting may be missing several bottom rows or several columns from the right hand side.
2)By using ("A65536") you are not taking into account that some of us may be working on Office 2007 which has over a million rows.
3)Better start declaring rows AND columns as long (due to XL 2007 again).

Here is how I'd go about it:
Sub DeleteRowsContainingBlanks()
''''''''''''''''''''''''''''''''''''''''''''''''''''
'First, set the Range of the Worksheet that contains
'anything, be it numbers, text or formulas
'ActiveSheet may be substituted by any Worksheet
''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim rngRange As Range
'Find the real last cell
On Error Resume Next
With ActiveSheet
lngLastRow = .Cells.Find("*", Range("A1"), _
xlFormulas, , xlByRows, xlPrevious).Row
lngLastColumn = .Cells.Find("*", Range("A1"), _
xlFormulas, , xlByColumns, xlPrevious).Column
End With
'Check if no data exists at all
If Err Then
Err.Clear
MsgBox "The Worksheet is empty", vbExclamation, "Message"
Exit sub
End If
On Error GoTo 0
'Set the Range that contains data
With ActiveSheet
Set rngRange = .Range(.Cells(1, 1), .Cells(lngLastRow, lngLastColumn))
End With
''''''''''''''''''''''''''''''''''''
'Delete the rows that contain blanks
''''''''''''''''''''''''''''''''''''
With rngRange
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

[jammer6_9]

:bug: Yeah it was all rows what Symon asking.. 'All apologies :bug:


Jammer, the code you are using there will only delete blank rows not rows that contain blanks.

[Simon Lloyd]

Tstav, thanks for that input and explanation, i am using Excel 2007 at the moment and hate it!, i find it very difficult to use as nothing seems to be in the place it was in 2003, 2003 was much more user friendly!

But i take your point!