Delete rows where colums 4 is empty

[Robert]

Hi All,

Could someone perhaps advice which VBA statement to use for: delete
all rows within a certain sheet where column 4 is empty

Many thanks in advance!

Regards,
Robert

 

[Guest]

The trick with this problem is you need two counter. One to keep track of
the number of times to loop. the 2nd count is to keep track of the row number


LastRow = cells(Rows.Count,4).end(xlup).Row
loopCount = 1
rowcount = 1
do while loopcount <= Lastrow

if isempty(cells(rowcount,4)) then

cells(rowcount,4).entirerow.delete
else
RowCount = rowcount + 1
end if

loopcount = loopcount + 1
loop

 

[Guest]

Another way

Dim myDeleteRange As Range
Dim r As Range
Dim lRow As Long

Set aWS = ActiveSheet

Set myRange = aWS.Cells(aWS.Rows.Count, 1).Resize(1, aWS.Columns.Count)
Debug.Print myRange.Address
lRow = myRange.End(xlUp).Row
Debug.Print lRow

Set myRange = aWS.Cells(1, 4).Resize(lRow, 1)
Debug.Print myRange.Address

Set myDeleteRange = Nothing

For Each r In myRange
If IsEmpty(r) Then
If myDeleteRange Is Nothing Then
Set myDeleteRange = r
Else
Set myDeleteRange = Union(myDeleteRange, r)
End If
End If
Next r

 

[Guest]

Barb nice code, but you forgot to do the actual delete.
You should add
myDeleteRange.entirerow.delete

 

[Guest]

if they are really empty

Sub Deleterows()
dim r as Range
On Error Resume Next
Worksheets("Sheet1").Columns(4).specialCells(xlBlanks).Entirerow.delete
On goto 0
end Sub

If you have more than 8192 separate areas (not just cells) in column 4 that
are blank, this will not work, but that is rarely the case.

 

[Guest]

If there are more than 8192 ares, can you run the code twice or more to
eliminate all the row?

 

[Guest]

You would need to build the code to work in increments that would result in
less than 8192 rows.

Ron de Bruin has documents some approaches:

http://www.rondebruin.nl/specialcells.htm

 

[Guest]

DOH! You're right. Thanks for the correction.

Barb nice code, but you forgot to do the actual delete.
You should add
myDeleteRange.entirerow.delete