PDA

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



frank_m
01-01-2011, 04:23 AM
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
01-01-2011, 05:02 AM
Edit: I need this to be done using VBA code


Yes because Excel Does Provide you with Filters ;)

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

frank_m
01-01-2011, 05:26 AM
Thanks shrivallabha

that works nicely

Bob Phillips
01-01-2011, 05:33 AM
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

shrivallabha
01-01-2011, 08:54 AM
Terrific XLD...no loops...:bow:

frank_m
01-01-2011, 10:51 AM
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 :bow: