Automate deletion process

J

JanM

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?
 
B

Billy Liddel

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
 
B

Billy Liddel

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
 
G

Gord Dibben

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
 
Top