Solved: Excel 2003 - Delete rows with no values in Columns A thru E

[frank_m]

I have a worksheet where Columns A thru J are used for data
and I would like to delete rows when all the cells in Columns A thru E are empty. (approx 1,000 out of 15,000 rows meet that criteria)
* Column J can be used as the last row reference.

Edit: I need this to be done using VBA code

Thanks

[shrivallabha]

Edit: I need this to be done using VBA code

Yes because Excel Does Provide you with Filters

[VBA]Sub deleteAtoE()
Dim LastRow As Long
With Sheet1
LastRow = .Range("J" & Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If .Range("A" & i).Value & .Range("B" & i).Value & _
.Range("C" & i).Value & .Range("D" & i).Value & _
.Range("E" & i).Value = "" Then
.Rows(i).delete
End If
Next i
End With
End Sub[/VBA]

[frank_m]

Thanks shrivallabha

that works nicely

[Bob Phillips]

[vba]

Sub DeleteBalanks()
Dim Lastrow As Long
Dim rng As Range

Application.ScreenUpdating = False

With ActiveSheet

.Rows(1).Insert
.Columns("F").Insert
.Range("F1").Value = "Temp"
Lastrow = .UsedRange.Rows.Count
Set rng = .Range("F2").Resize(Lastrow - 1)
rng.FormulaR1C1 = "=COUNTIF(RC[-5]:RC[-1],"""")=5"
Set rng = .Range("F1").Resize(Lastrow)
rng.AutoFilter Field:=1, Criteria1:="TRUE"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
.Columns("F").Delete
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[shrivallabha]

Terrific XLD...no loops...

[frank_m]

Hi xld

Thank you very much for writing that code for me. --> as shrivallabha said the fact that there is no looping is great. Definitely worth plenty of praise