ExcelBanter - delete rows in a given range

In order for this code to work for me, I had to change the line:

lngLastRow = wks.Cells(Rows.Count, "A").End(xlUp).End

to:

lngLastRow = wks.Cells(Rows.Count, "A").End(xlUp).Row

Using the original line resulted in Run-time error '450': "Wrong number of
arguments or invalid property assignment"

Otherwise, once I changed the reference to the value I was testing for it
worked flawlessly and the logic was just what I needed.

Thanks Ryan.

"Ryan H" wrote:

Just adjust the worksheet name, first row, and last row variables and it
should work for you. If you don't want the entire row deleted let me know.
If this helps click "YES" below.

Sub DeleteRows()

Dim wks As Worksheet
Dim lngFirstRow As Long
Dim lngLastRow As Long

Set wks = Sheets("Sheet1")
lngFirstRow = 2
lngLastRow = wks.Cells(Rows.Count, "A").End(xlUp).End

' clears empty rows from sheet
With wks
For i = lngLastRow To lngFirstRow Step -1
If Trim(.Cells(i, "A")) = "" Then
.Cells(i, "A").EntireRow.Delete Shift:=xlUp
End If
Next i
End With
End Sub
--
Cheers,
Ryan

"Wen" wrote:

Anyone has a good sub to delete from a given range any row with blank index
cell?
For example, I have a table range with column A titled "Name" as index
column. I need a sub that deletes any row where column A is blank, but say,
column C has non-blank value. it is not the same as deleting all blank rows
in a range.
TIA.
Wen

.