This is my formula in each row in my table has an if/then statement to pick
up corresponding values from the same rows in other worksheets if the value
in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
want to be able to delete the rows with a "" value. I have tried to write a
Do/Until loop to look at each row in the table, but I cannot figure out how
to word the statement telling it what to do when it encounters the "" values.

I would recommend using a For...Each Loop. The For...Each is more efficient
than a Do...Loop when you are working with objects. This procedure below
will scan down Col. A looking for values = "", if it finds one the entire row
is deleted.

Sub DeleteRows()

Dim LastRow As Long
Dim myRange As Range
Dim cell As Range

' finds last row in Col.A
LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

' range of cells to test values
Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1))

' loop thru each cell in myRange to find "" values
For Each cell In myRange
If cell.Value = "" Then
cell.EntireRow.Delete
End If
Next cell

End Sub

Hope this helps. If so please click "yes" below.
--
Cheers,
Ryan


"marcia2026" wrote:

> This is my formula in each row in my table has an if/then statement to pick
> up corresponding values from the same rows in other worksheets if the value
> in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
> want to be able to delete the rows with a "" value. I have tried to write a
> Do/Until loop to look at each row in the table, but I cannot figure out how
> to word the statement telling it what to do when it encounters the "" values.

Sub remove_rows()
cl = "A"
n = Cells(Rows.Count, cl).End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, cl).Value = "" Then
Cells(i, cl).EntireRow.Delete
End If
Next
End Sub

looks for blanks in column A. Adjust to suit your needs.
--
Gary''s Student - gsnu200795


"marcia2026" wrote:

> This is my formula in each row in my table has an if/then statement to pick
> up corresponding values from the same rows in other worksheets if the value
> in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
> want to be able to delete the rows with a "" value. I have tried to write a
> Do/Until loop to look at each row in the table, but I cannot figure out how
> to word the statement telling it what to do when it encounters the "" values.

Hi Ryan, I copied your code into my worksheet, when I tried to run it I
received the message "subscript out of range"

thanks,
marcia

"RyanH" wrote:

> I would recommend using a For...Each Loop. The For...Each is more efficient
> than a Do...Loop when you are working with objects. This procedure below
> will scan down Col. A looking for values = "", if it finds one the entire row
> is deleted.
>
> Sub DeleteRows()
>
> Dim LastRow As Long
> Dim myRange As Range
> Dim cell As Range
>
> ' finds last row in Col.A
> LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
>
> ' range of cells to test values
> Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1))
>
> ' loop thru each cell in myRange to find "" values
> For Each cell In myRange
> If cell.Value = "" Then
> cell.EntireRow.Delete
> End If
> Next cell
>
> End Sub
>
> Hope this helps. If so please click "yes" below.
> --
> Cheers,
> Ryan
>
>
> "marcia2026" wrote:
>
> > This is my formula in each row in my table has an if/then statement to pick
> > up corresponding values from the same rows in other worksheets if the value
> > in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
> > want to be able to delete the rows with a "" value. I have tried to write a
> > Do/Until loop to look at each row in the table, but I cannot figure out how
> > to word the statement telling it what to do when it encounters the "" values.

worked. Thanks

"Gary''s Student" wrote:

> Sub remove_rows()
> cl = "A"
> n = Cells(Rows.Count, cl).End(xlUp).Row
> For i = n To 1 Step -1
> If Cells(i, cl).Value = "" Then
> Cells(i, cl).EntireRow.Delete
> End If
> Next
> End Sub
>
> looks for blanks in column A. Adjust to suit your needs.
> --
> Gary''s Student - gsnu200795
>
>
> "marcia2026" wrote:
>
> > This is my formula in each row in my table has an if/then statement to pick
> > up corresponding values from the same rows in other worksheets if the value
> > in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
> > want to be able to delete the rows with a "" value. I have tried to write a
> > Do/Until loop to look at each row in the table, but I cannot figure out how
> > to word the statement telling it what to do when it encounters the "" values.

Sorry. I spoke too soon. Actually, this one didn't work either.

"marcia2026" wrote:

> worked. Thanks
>
> "Gary''s Student" wrote:
>
> > Sub remove_rows()
> > cl = "A"
> > n = Cells(Rows.Count, cl).End(xlUp).Row
> > For i = n To 1 Step -1
> > If Cells(i, cl).Value = "" Then
> > Cells(i, cl).EntireRow.Delete
> > End If
> > Next
> > End Sub
> >
> > looks for blanks in column A. Adjust to suit your needs.
> > --
> > Gary''s Student - gsnu200795
> >
> >
> > "marcia2026" wrote:
> >
> > > This is my formula in each row in my table has an if/then statement to pick
> > > up corresponding values from the same rows in other worksheets if the value
> > > in the subsequent worksheet is greater than 0, otherwise leave blank. Now I
> > > want to be able to delete the rows with a "" value. I have tried to write a
> > > Do/Until loop to look at each row in the table, but I cannot figure out how
> > > to word the statement telling it what to do when it encounters the "" values.

ryan -
for-next or do-loop won't work in deleting rows moving from the top to
the bottom.......... if you are on row 19 & delete the entire row, row
20 moves up into row 19, but the macro goes to row 20. so you've
missed a row. that's why gary went from the bottom up to the top -
that way when you delete the row you don't miss one.

susan



On Jul 15, 12:50*pm, RyanH <Ry...@discussions.microsoft.com> wrote:
> I would recommend using a For...Each Loop. *The For...Each is more efficient
> than a Do...Loop when you are working with objects. *This procedure below
> will scan down Col. A looking for values = "", if it finds one the entire row
> is deleted.
>
> Sub DeleteRows()
>
> Dim LastRow As Long
> Dim myRange As Range
> Dim cell As Range
>
> * * ' finds last row in Col.A
> * * LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
>
> * * ' range of cells to test values
> * * Set myRange = Sheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 1))
>
> * * ' loop thru each cell in myRange to find "" values
> * * For Each cell In myRange
> * * * * If cell.Value = "" Then
> * * * * * * cell.EntireRow.Delete
> * * * * End If
> * * Next cell
>
> End Sub
>
> Hope this helps. *If so please click "yes" below.
> --
> Cheers,
> Ryan
>
>
>
> "marcia2026" wrote:
> > This is my formula in each row in my table has an if/then statement to pick
> > up corresponding values from the same rows in other worksheets if the value
> > in the subsequent worksheet is greater than 0, otherwise leave blank. *Now I
> > want to be able to delete the rows with a "" value. *I have tried to write a
> > Do/Until loop to look at each row in the table, but I cannot figure outhow
> > to word the statement telling it what to do when it encounters the "" values.- Hide quoted text -
>
> - Show quoted text -

this is the same (basically) as gary's, but the variables are
declared..........
===========================
Option Explicit

Sub step_backwards()

Dim myLastRow As Long
Dim r As Long
Dim c As Range

myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row

For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "" Then
c.EntireRow.Delete
End If
Next r

End Sub
=========================
again, it looks for blanks in column A.
hope it helps
susan



On Jul 15, 2:08*pm, marcia2026 <marcia2...@discussions.microsoft.com>
wrote:
> Sorry. *I spoke too soon. *Actually, this one didn't work either.
>
>
>
> "marcia2026" wrote:
> > worked. *Thanks
>
> > "Gary''s Student" wrote:
>
> > > Sub remove_rows()
> > > cl = "A"
> > > n = Cells(Rows.Count, cl).End(xlUp).Row
> > > For i = n To 1 Step -1
> > > * * If Cells(i, cl).Value = "" Then
> > > * * * * Cells(i, cl).EntireRow.Delete
> > > * * End If
> > > Next
> > > End Sub
>
> > > looks for blanks in column A. *Adjust to suit your needs.
> > > --
> > > Gary''s Student - gsnu200795
>
> > > "marcia2026" wrote:
>
> > > > This is my formula in each row in my table has an if/then statementto pick
> > > > up corresponding values from the same rows in other worksheets if the value
> > > > in the subsequent worksheet is greater than 0, otherwise leave blank. *Now I
> > > > want to be able to delete the rows with a "" value. *I have triedto write a
> > > > Do/Until loop to look at each row in the table, but I cannot figureout how
> > > > to word the statement telling it what to do when it encounters the "" values.- Hide quoted text -
>
> - Show quoted text -

Skip the loops altogether:

This looks in column a for mts and deletes the rows when found:
Sub RemoveMTCellRows()
On Error Resume Next

ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Cliff Edwards