deleting cetain rows that match a criteria

  • Thread starter Thread starter electric_d
  • Start date Start date
E

electric_d

I was wondering how to do this. I am making a system for a library an
wanted to delete all the overdue books automatically (and sent t
another location). I have tried an advanced filter but this only hide
the rows and doesnt delete them. I have a few ideas but im not sur
how to go about them. I sort them by date so all overdue books are a
the top, then create a function (COUNTIF) to count the number of cell
with a date after todays. then maybe put this into a macro...telling i
how many rows to be deleted from the top of the table.

thanx for reading
 
You can do the inverse test, show the books overdue, and then use advanced
filter to copy them, or filter them and delete the visible rows.

If you want it more automated, you need VBA.

Surely, there is already a product on the market place that would do all
this sort of library book management?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Electric_d,

You can try this VBA

Sub DeleteSomeRows()
Range("D7").Select
Do
If ActiveCell.Value = "yes" Then
ActiveCell.Rows("1:1").EntireRow.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "" Then End
Loop
End Sub

Where your 'overdue' critenion is in Column D and the list starts in Row 7.
Change the "yes" to whatever you have used to flag the overdue books.

This will keep your list sort order intact.

Hiran
 
thanx for the help ill check both out.

im just doing this as a part of my A level, it doesnt always make sens
 
part of my A level, it doesnt always make sense<<
Hey cool. Post back if you want to know what each line does.

Hiran
 
thanx for the help. I have another lil question. I need these rows t
be copied into another location before deleting them. Is this simila
to the previous VBA script?

I got the 'delete certain rows' script working and its gr8, ta

thnax for ur time
 
Aaaaaaaaaaaaaah.
One way is to get the deleted row to be copied to another sheet inside the
loop. But that requires a lot more thinking and lines of code. Instead ...

An easier option is to copy the data to another sheet and run the same code
but changing the 'yes' to 'no'. You can do this with code (if it needs to be
run repeatedly) - best way is to record a macro, and then insert the code I
gave you earlier at the appropriate point.

Hiran
 
Back
Top