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

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

"Robert" wrote:

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

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

"Robert" wrote:

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

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


"Barb Reinhardt" wrote:

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

"Robert" wrote:

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

if they are really empty

Sub Deleterows()
dim r as Range
On Error Resume Next
Worksheets("Sheet1").Columns(4).specialCells(xlBla nks).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.

--
Regards,
Tom Ogilvy


"Robert" wrote:

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

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

"Tom Ogilvy" wrote:

if they are really empty

Sub Deleterows()
dim r as Range
On Error Resume Next
Worksheets("Sheet1").Columns(4).specialCells(xlBla nks).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.

--
Regards,
Tom Ogilvy


"Robert" wrote:

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

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

--
Regards,
Tom Ogilvy


"Joel" wrote:

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

"Tom Ogilvy" wrote:

if they are really empty

Sub Deleterows()
dim r as Range
On Error Resume Next
Worksheets("Sheet1").Columns(4).specialCells(xlBla nks).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.

--
Regards,
Tom Ogilvy


"Robert" wrote:

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

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

"Joel" wrote:

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


"Barb Reinhardt" wrote:

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

"Robert" wrote:

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