How to clear a row with an empty cell in one of its columns, without deleting entire row?

1
votes

A seemingly simple issue:

I have data in Columns A:E. Column E has some blank cells in some of the rows.

I would like to remove ALL THE ROW that include a blank cell in E. However, Here's the catch, there is other data in subsequent columns. if I delete the entire row, this data will be also deleted, which I don't want.

To be more specific, I need to: (1) Check column E for blank cells (2) When a blank cell if found, clear the row that has this cell, but only Columns A:E (3) Shift the data in Columns A:E up

I tried:

Range("E2:E100").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

But this one only shifts data in column E, not the entire row.

of course, i can use:

Range("E2:E100").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

But like I said, this will delete data in subsequent columns, which I don't want.

Any tips?

Thanks,

Al

2
excelvbarowshift
Won't that algorithm always lead to only empty rows from A to E ? And I believe what you are looking for is Range("Ax:Ey").ClearContents where x is your start row and y your end row. To find x such that it is equivalent to shift+up, just do cell.ToEnd(xlUp).Row where cell is your Range variable you use to loop through your rows. – ApplePie

2 Answers

2
votes

You can't shift the entire and leave some of the row behind, that is a contradiction. It sounds like you want to do this:

Range("A" & row & ":E" & row).Delete Shift:=xlUp

Where row is the row number you want to delete

0
votes

To remove A:E in "chunks" but preserve you other columns intact a full solution is this

Sub PartKill2()
Dim rng1 As Range
Dim rng2 As Range
ActiveSheet.UsedRange
On Error Resume Next
Set rng1 = Columns("E").SpecialCells(xlBlanks)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
For Each rng2 In rng1.Areas
rng2.Cells(1).Offset(0, -4).Resize(rng2.Rows.Count, 5).Delete xlUp
Next
End Sub

If you wanted to delete the entire row where E was blank, and columns F:End were blank (but otherwise leave the row as is) then this more complex version can be used

Sub PartKill1()
Dim rng1 As Range
Dim lngCell As Long
Dim lngArea As Long
ActiveSheet.UsedRange
On Error Resume Next
Set rng1 = Columns("E").SpecialCells(xlBlanks)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
For lngArea = rng1.Areas.Count To 1 Step -1
For lngCell = rng1.Areas(lngArea).Cells.Count To 1 Step -1
If Application.CountA(Range(rng1.Areas(lngArea).Cells(lngCell).Offset(0, 1), Cells(rng1.Areas(lngArea).Cells(lngCell).Row, Columns.Count))) = 0 Then
rng1.Areas(lngArea).Cells(lngCell).EntireRow.Delete
End If
Next
Next
End Sub