How can I move and entire row upon cell completion?

I am creating a punch list for our office. This is basically a list of jobs
that need to be finished and a date for when they were entered and the job
name. I would like to have the entire cell move to either the bottom of the
list or another sheet in the workbook upon completion of the date completed
field or a checkbox or something. Can this be done?

It can be done, but why not just leave it where it is and use data|filter to
show or hide that row.

Or you could just sort the data by that column.

Tony wrote:
>
> I am creating a punch list for our office. This is basically a list of jobs
> that need to be finished and a date for when they were entered and the job
> name. I would like to have the entire cell move to either the bottom of the
> list or another sheet in the workbook upon completion of the date completed
> field or a checkbox or something. Can this be done?

--

Dave Peterson

Assume data starts in A1 with header information in row 1 and data below, no
completely blank rows or columns within the data. Assume completion date is
column J.

right click on the sheet tab and select view code. In the resulting module
put in code like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
' if change was in column 10
Application.EnableEvents = False
Set rng = Range("A1").CurrentRegion.Columns(10)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo ErrHandler
If Not rng1 Is Nothing Then
rng1.Formula = "=NA()"
End If
If Target.Column = 10 Then
Range("A1").CurrentRegion.Sort _
Key1:=Range("J10"), _
Order1:=xlDescending, _
header:=xlYes
End If
Set rng1 = Nothing
On Error Resume Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)
rng1.ClearContents
On Error GoTo ErrHandler
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy




"Tony" <[email protected]> wrote in message
news:[email protected]...
> I am creating a punch list for our office. This is basically a list of
jobs
> that need to be finished and a date for when they were entered and the job
> name. I would like to have the entire cell move to either the bottom of
the
> list or another sheet in the workbook upon completion of the date
completed
> field or a checkbox or something. Can this be done?

Dave,

If would like to keep the row visible somewhere so that it can be looked
back on. Can I do this with a data filter? The sorting doesn't work because
the items are blank to begin. If I add something in ascending or descending
it puts the cells with something in them first.

"Dave Peterson" wrote:

> It can be done, but why not just leave it where it is and use data|filter to
> show or hide that row.
>
> Or you could just sort the data by that column.
>
> Tony wrote:
> >
> > I am creating a punch list for our office. This is basically a list of jobs
> > that need to be finished and a date for when they were entered and the job
> > name. I would like to have the entire cell move to either the bottom of the
> > list or another sheet in the workbook upon completion of the date completed
> > field or a checkbox or something. Can this be done?
>
> --
>
> Dave Peterson
>

If you select the whole range, then do data|filter|autofilter, you can use the
dropdown arrow to choose the date you want to see--or blanks or non-blanks.



Tony wrote:
>
> Dave,
>
> If would like to keep the row visible somewhere so that it can be looked
> back on. Can I do this with a data filter? The sorting doesn't work because
> the items are blank to begin. If I add something in ascending or descending
> it puts the cells with something in them first.
>
> "Dave Peterson" wrote:
>
> > It can be done, but why not just leave it where it is and use data|filter to
> > show or hide that row.
> >
> > Or you could just sort the data by that column.
> >
> > Tony wrote:
> > >
> > > I am creating a punch list for our office. This is basically a list of jobs
> > > that need to be finished and a date for when they were entered and the job
> > > name. I would like to have the entire cell move to either the bottom of the
> > > list or another sheet in the workbook upon completion of the date completed
> > > field or a checkbox or something. Can this be done?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson