Re: great but error box

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 17:04:46 -0500

Sub Deleteemptyrows()
 Dim varr as Variant
 Dim rng As Range
 varr = Array("qty_range","qty_range1", "qty_range2")
  for i = lbound(varr) to ubound(varr)
  Set rng = Range(varr(i))
  Set rng = Intersect(rng.EntireRow, Columns(3)).Cells
  On Error Resume Next
    Set rng = rng.SpecialCells(xlBlanks)
    rng.EntireRow.Delete
  On Error goto 0
  Next
End Sub

or

Sub Deleteemptyrows()
 Dim varr as Variant
 Dim rng As Range
 Set rng = Union(Range("qty_range"), _
      Range("qty_range1"), Range("qty_range2"))
  Set rng = Range(varr(i))
  Set rng = Intersect(rng.EntireRow, Columns(3)).Cells
  On Error Resume Next
  Set rng = rng.SpecialCells(xlBlanks)
  rng.EntireRow.Delete
  On Error goto 0
End Sub

so nest the SpecialCells command and the delete command within

On Error Resume Next

On Error goto 0 

-- 
Regards,
Tom Ogilvy
"Todd" <anonymous@discussions.microsoft.com> wrote in message
news:9cae01c4061e$87326970$a501280a@phx.gbl...
> wow! thanks
>
> But May I impose yet further and ask how to handle when a
> user pushes the button a second time and gets the error
> message
>              run time error 1004
>              no cells where found
>
> there could be a message box with a click close , there
> could be a disapaerring button , it could jsut sit there
> and do nothing - what ever is quickest to suggest and
> simpliest for me (novice)
>
> >-----Original Message-----
> >Assuming you mean you have several sections of Column C
> that you would like
> >to examine and they have separate range names
> >
> >Sub Deleteemptyrows()
> > Dim varr as Variant
> > Dim rng As Range
> > varr = Array("qty_range","qty_range1", "qty_range2")
> >  for i = lbound(varr) to ubound(varr)
> >  Set rng = Range(varr(i))
> >  Set rng = Intersect(rng.EntireRow, Columns(3)).Cells
> >  Set rng = rng.SpecialCells(xlBlanks)
> >  rng.EntireRow.Delete
> >  Next
> >End Sub
> >
> >or
> >
> >Sub Deleteemptyrows()
> > Dim varr as Variant
> > Dim rng As Range
> > Set rng = Union(Range("qty_range"), _
> >      Range("qty_range1"), Range("qty_range2"))
> >  Set rng = Range(varr(i))
> >  Set rng = Intersect(rng.EntireRow, Columns(3)).Cells
> >  Set rng = rng.SpecialCells(xlBlanks)
> >  rng.EntireRow.Delete
> >End Sub
> >
> >-- 
> >Regards,
> >Tom Ogilvy
> >
> >
> >
> >"Todd" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:a02f01c40618$47de76c0$a101280a@phx.gbl...
> >> Thank you Tom & Frank both for your time
> >>
> >> I ran the Tom's macro and It gave me ref. eroors in the
> >> formaul fields in the remaining rows BUT I MADE A
> MISTAKE
> >> when I said column B - it was really column C so I took
> a
> >> guess and changed the 2 to a 3 ( 4th row from bottom and
> >> it worked with no formula errors.
> >>
> >> Also Can a second non adjacent range be added to this to
> >> go down the *** may a few rows and then delete a
> second
> >> and even a third named range.
> >>
> >> This is the macro now:
> >>
> >> Sub Deleteemptyrows()
> >>  Dim rng As Range
> >>   Set rng = Range("qty_range")
> >>   Set rng = Intersect(rng.EntireRow, Columns(3)).Cells
> >>   Set rng = rng.SpecialCells(xlBlanks)
> >>   rng.EntireRow.Delete
> >> End Sub
> >>
> >>
> >> >-----Original Message-----
> >> >You had a stray "1" in the For command and missed the
> >> underscore in the
> >> >qty_range.
> >> >
> >> >Sub delete_zero_qty_rows_original_macro()
> >> >dim last_row as long
> >> >Dim rng as Range
> >> >Dim row_index as Long
> >> >    Active***.Unprotect
> >> >set rng = Range("qty_range")
> >> >last_row = rng.rows.count + rng.row -1
> >> >
> >> >        For  row_index = last_row to rng.row step -1
> >> >            If IsEmpty(cells(row_index,"B").Value) Then
> >> >                cells(row_index,"B").entirerow.delete
> >> >            End If
> >> >        Next
> >> >    Range("D8").Select
> >> >    Active***.Unprotect
> >> >End Sub
> >> >
> >> >Another approach:
> >> >
> >> >Sub Deleteemptyrows()
> >> > dim rng as Range
> >> >  set rng = Range("qty_range")
> >> >  set rng = intersect(rng.Entirerow,columns(2)).Cells
> >> >  set rng = rng.Specialcells(xlBlanks)
> >> >  rng.Entirerow.Delete
> >> >End Sub
> >> >
> >> >-- 
> >> >Regards,
> >> >Tom Ogilvy
> >> >
> >> >
> >> >"Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >> >news:ONI3q8gBEHA.1964@TK2MSFTNGP11.phx.gbl...
> >> >> Hi
> >> >> try (not fully tested)
> >> >>
> >> >> Sub delete_zero_qty_rows_original_macro()
> >> >> dim last_row as long
> >> >> dim rng as range
> >> >> Dim row_index
> >> >>     Active***.Unprotect
> >> >> set rng = Range("qty range")
> >> >> last_row = rng.rows.count + rng.row -1
> >> >>
> >> >>         For  row_index = last_row to 1 rng.row step -
> 1
> >> >>             If IsEmpty(cells(row_index,"B").Value)
> Then
> >> >>                 cells(row_index,"B").entirerow.delete
> >> >>             End If
> >> >>         Next
> >> >>     Range("D8").Select
> >> >>     Active***.Unprotect
> >> >> End Sub
> >> >>
> >> >>
> >> >> -- 
> >> >> Regards
> >> >> Frank Kabel
> >> >> Frankfurt, Germany
> >> >>
> >> >> Todd wrote:
> >> >> > Hello I would like to modify the 2nd macro that
> works
> >> and
> >> >> > hides rows by a cell in "column b" being empty to
> the
> >> 1st
> >> >> > macro which deletes rows based on cell in "column
> b"
> >> being
> >> >> > empty.
> >> >> >
> >> >> > Please note I will have rows above and bleow the
> named
> >> >> > range of qty rows that need to stay. This is a
> parts
> >> list
> >> >> > *** with profit calcs out the right hand side &
> I am
> >> >> > going to delete those parts that do not have a qty
> >> entered
> >> >> > in a particular column ( in this case "B")
> >> >> >
> >> >> > If you do not like my barrowed macro then please
> >> suggest
> >> >> > something. I am also looking to delete same range
> >> same way
> >> >> > on another tab in the same work book but that is a
> >> nice to
> >> >> > have - the 1st part is the important issue).
> >> >> >
> >> >> > This is first macro is the modified to delete
> macro:
> >> >> >
> >> >> > Sub delete_zero_qty_items()
> >> >> >     Active***.Unprotect
> >> >> >     'qty_range is a named range in column b that
> goes
> >> from
> >> >> > B25 to B110
> >> >> >         For Each c In Range("qty_range")
> >> >> >             If IsEmpty(c.Value) Then
> >> >> >                 c.EntireRow.Delete
> >> >> >             End If
> >> >> >         Next c
> >> >> >     Range("D8").Select
> >> >> >     Active***.Unprotect
> >> >> > End Sub
> >> >> >
> >> >> > This second macro is the original macro that I
> tried
> >> to
> >> >> > change.
> >> >> >
> >> >> > Sub hide_zero_qty_rows_original_macro()
> >> >> >     Active***.Unprotect
> >> >> >         For Each c In Range("qty range")
> >> >> >             If IsEmpty(c.Value) Then
> >> >> >                 c.EntireRow.Hidden = True
> >> >> >             End If
> >> >> >         Next c
> >> >> >     Range("D8").Select
> >> >> >     Active***.Unprotect
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > Thank yo for your time
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Quantcast