Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional delete | Excel Discussion (Misc queries) | |||
Conditional Delete | New Users to Excel | |||
Conditional Row Delete | Excel Programming | |||
Conditional Row Delete | Excel Programming | |||
CONDITIONAL DELETE | Excel Programming |