Automate deletion process

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JanM - 13 Jun 2007 17:00 GMT

How do I write a function or macro that will look at what is in a cell,
validate it, if correct delete the row, and if invalid just move down a row
and continue until the end of data in a column?

Signature

Jan M

Reply to this Message

Billy Liddel - 13 Jun 2007 17:54 GMT

The following macro looks for duplicates accross cols a, b, and c then
deletes the duplicate rows. It will give you an idea to to work on.

Regards
Peter

> How do I write a function or macro that will look at what is in a cell,
> validate it, if correct delete the row, and if invalid just move down a row
> and continue until the end of data in a column?

Reply to this Message

JanM - 13 Jun 2007 18:43 GMT

I'm sorry, but I don't see the macro.

Thanks,
Jan

Signature

Jan M

> The following macro looks for duplicates accross cols a, b, and c then
> deletes the duplicate rows. It will give you an idea to to work on.
[quoted text clipped - 5 lines]
> > validate it, if correct delete the row, and if invalid just move down a row
> > and continue until the end of data in a column?

Reply to this Message

Billy Liddel - 13 Jun 2007 21:25 GMT

Oops - sorry Jan

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub

Regards
Peter

> I'm sorry, but I don't see the macro.
>
[quoted text clipped - 10 lines]
> > > validate it, if correct delete the row, and if invalid just move down a row
> > > and continue until the end of data in a column?

Reply to this Message

Gord Dibben - 13 Jun 2007 21:54 GMT

Jan

Sub Delete_Valid_Rows()
findstring = "valid" 'substitute your validation parameter here
Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole)
Wend
End Sub

Gord Dibben MS Excel MVP

>How do I write a function or macro that will look at what is in a cell,
>validate it, if correct delete the row, and if invalid just move down a row
>and continue until the end of data in a column?

Reply to this Message