conditional delete

M

Miree

I have sheet 1 and sheet 2, i need to write a code that perfoms the following
action

If the contents of cell in (sheet2 column B) does NOT appear in (sheet 2
column A) delete the line, all of the cells in question are numbers only.

I am learning macros as i go, i already have a few set up to delete based on
text box content that I understand, working from this would help me know what
I am doing.

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "DM"), Cells(Rows.Count, "DM").End(xlUp))

'Work backwards from bottom to top when deleting rows

With rng
For i = .Rows.Count To 1 Step -1
If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox10.Text)) = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With

All help much appreciated
Thank you
 
P

Patrick Molloy

For rw = lastRow To firstRow ' of columnB
If notMatched(Cells(rw, "B")) Then

.Cells(i).EntireRow.Delete

End If
Next

use a function for matching - VBA will error if a VLOOKUP or MATCH function
fails to match the passed parameter....

Function notMatched(text As String) As Boolean
Dim result As Long
On Error Resume Next 'trap the no match error
result = WorksheetFunction.Match(text, Range("A:A"), False)
notMatched = (result = 0)
I have sheet 1 and sheet 2, i need to write a code that perfoms the following
action

If the contents of cell in (sheet2 column B) does NOT appear in (sheet 2
column A) delete the line, all of the cells in question are numbers only.

I am learning macros as i go, i already have a few set up to delete based on
text box content that I understand, working from this would help me know what
I am doing.

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "DM"), Cells(Rows.Count, "DM").End(xlUp))

'Work backwards from bottom to top when deleting rows

With rng
For i = .Rows.Count To 1 Step -1
If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox10.Text)) = 0 Then

IF worksheet
 
M

Miree

Im not entirely sure what you are suggesting I do, could you please explain a
little more

Thank you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top