conditional delete
 

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

conditional delete
 

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)
On Error GoTo 0
End Function


"Miree" wrote:

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

.Cells(i).EntireRow.Delete
End If
Next i
End With

All help much appreciated
Thank you

conditional delete
 

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

Thank you

"Patrick Molloy" wrote:

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)
On Error GoTo 0
End Function


"Miree" wrote:

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

.Cells(i).EntireRow.Delete
End If
Next i
End With

All help much appreciated
Thank you