Deleting unwanted data from a sheet.

**Christopherdj** · 04-30-2012, 02:02 AM

I am looking for a solution to remove any row without data in Column E of the attached file.
If an entry appears in column E then I require all data in that row kept.
It is a bit like sorting data by column E and if nothing in Column E, then delete entire row.
If a sort function can be undertaken, then sort by column A.
I have deleted data that usually appears in columns A and B for privacy.

**jraj1106** · 04-30-2012, 02:13 AM

Hi

Try this code:

Sub delrows()
Dim rcnt As Long

rcnt = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To rcnt
If Sheets("Sheet1").Range("A" & i).Value <> "" Then
If Sheets("Sheet1").Range("E" & i).Value = "" Then
Sheets("Sheet1").Rows(i & ":" & i).Delete
i = i - 1
End If
End If
Next
End Sub

Regards..

**Trebor76** · 04-30-2012, 02:39 AM

Hi Christopherdj,

Try this (initially on a copy of your data in case the results are not as expected):

Please Login or Register  to view this content.

HTH

Robert

**Sa DQ** · 04-30-2012, 03:20 AM

PHP Code:


Option Explicit
Sub DeleteBlankCellRows()
 Dim Rng As Range, dRng As Range
 Dim Rws As Long
 
 Rws = Cells.Rows.Count:        Rws = Cells(Rws, "A").End(xlUp).Row
 Set Rng = Range("E1:E" & Rws)
 Set dRng = Rng.SpecialCells(xlCellTypeBlanks).EntireRow
 If Not dRng Is Nothing Then dRng.Delete

End Sub