Alter Code From Delete Rows With Certain Value to Delete Rows Without Certain Value

[MrLupin]

I have the following code that deletes all rows containing a certain string provided through an input box.

Sub DeleteRows()
'Delete rows by selecting the range and entering the criteria.
'Range should be a single column. How to capture and enforce?
Dim i As Integer
Dim j As Integer
Dim selRange As Range
Dim crit As String 'Use Integer or Variable if not String.

Set selRange = Selection

'Cycle through rows in selRange and delete entire row when crit
'equals the current value.

j = 0
For i = selRange.Rows.Count To 1 Step -1
If crit = selRange.Cells(i, 1).Value Then
selRange.Cells(i, 1).EntireRow.Delete
j = j + 1

End If

Next i

I would like to change the code from equals (=) to does not equal (<>) as follows:

If crit = selRange.Cells(i, 1).Value Then

To This:

If crit <> selRange.Cells(i, 1).Value Then

It didn’t work - probably because I don’t understand exactly what is happening with the next three rows of code. Any help would be appreciated.

 

[Trebor76]

Hi MrLupin,

Welcome to MrExcel!!

See how this goes (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub DeleteRows()

    Dim ws As Worksheet
    Dim strCriteria As String, strCol As String
    Dim lngRow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing the data. Change to suit if necessary.
    strCol = "A" '<-Column containing text to be checked for deleting row(s). Change to suit if necessary.
    strCriteria = InputBox("Enter the text you wish to delete rows from Col. " & StrConv(strCol, vbUpperCase) & ":", "Delete Row Text")
    If Len(strCriteria) = 0 Then Exit Sub 'Quit as Cancel was pressed
    
    Application.ScreenUpdating = False
    
    For lngRow = ws.Cells(Rows.Count, strCol).End(xlUp).Row To 1 Step -1
        If StrConv(ws.Range(strCol & lngRow), vbUpperCase) <> StrConv(strCriteria, vbUpperCase) Then
            ws.Rows(lngRow).Delete
        End If
    Next lngRow
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[MrLupin]

Hi MrLupin,

Welcome to MrExcel!!

See how this goes (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub DeleteRows()

    Dim ws As Worksheet
    Dim strCriteria As String, strCol As String
    Dim lngRow As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing the data. Change to suit if necessary.
    strCol = "A" '<-Column containing text to be checked for deleting row(s). Change to suit if necessary.
    strCriteria = InputBox("Enter the text you wish to delete rows from Col. " & StrConv(strCol, vbUpperCase) & ":", "Delete Row Text")
    If Len(strCriteria) = 0 Then Exit Sub 'Quit as Cancel was pressed
   
    Application.ScreenUpdating = False
   
    For lngRow = ws.Cells(Rows.Count, strCol).End(xlUp).Row To 1 Step -1
        If StrConv(ws.Range(strCol & lngRow), vbUpperCase) <> StrConv(strCriteria, vbUpperCase) Then
            ws.Rows(lngRow).Delete
        End If
    Next lngRow
   
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

Thanks a million! I'll try it now.

 

[MrLupin]

H

Hi MrLupin,

Welcome to MrExcel!!

See how this goes (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub DeleteRows()

    Dim ws As Worksheet
    Dim strCriteria As String, strCol As String
    Dim lngRow As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing the data. Change to suit if necessary.
    strCol = "A" '<-Column containing text to be checked for deleting row(s). Change to suit if necessary.
    strCriteria = InputBox("Enter the text you wish to delete rows from Col. " & StrConv(strCol, vbUpperCase) & ":", "Delete Row Text")
    If Len(strCriteria) = 0 Then Exit Sub 'Quit as Cancel was pressed
   
    Application.ScreenUpdating = False
   
    For lngRow = ws.Cells(Rows.Count, strCol).End(xlUp).Row To 1 Step -1
        If StrConv(ws.Range(strCol & lngRow), vbUpperCase) <> StrConv(strCriteria, vbUpperCase) Then
            ws.Rows(lngRow).Delete
        End If
    Next lngRow
   
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

That works very well. How would I change it so that I could input the sheet name each time? Dim another variable that replaces "sheet1"?

 

[Trebor76]

Try this where you separate the required criteria of sheet name and text with a dash (if the text has a dash you will have to use another separator):

Option Explicit
Sub DeleteRows()

    Dim ws As Worksheet
    Dim strCriteria As String, strCol As String
    Dim lngRow As Long
    
    strCol = "A" '<-Column containing text to be checked for deleting row(s). Change to suit if necessary.
    strCriteria = InputBox("Enter the sheet name and text you wish to base the deletion of rows on if the text in Col. " & StrConv(strCol, vbUpperCase) & " does not equal a given value separating each with a dash i.e." & vbNewLine & "Sheet1-Green:", "Set Sheet and Text Editor")
    If Len(strCriteria) = 0 Then Exit Sub 'Quit as Cancel was pressed
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets(CStr(Split(strCriteria, "-")(0)))
    
    For lngRow = ws.Cells(Rows.Count, strCol).End(xlUp).Row To 1 Step -1
        If StrConv(ws.Range(strCol & lngRow), vbUpperCase) <> StrConv(CStr(Split(strCriteria, "-")(1)), vbUpperCase) Then
            ws.Rows(lngRow).Delete
        End If
    Next lngRow
    
    Application.ScreenUpdating = True
    
End Sub