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(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.

--
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(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.
>
> --
> 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(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.
> >
> > --
> > 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
> > >
> > >