Excel VBA to Delete rows based on multiple criterias

[mchilapur]

Dear all,
Plz review the attached excel file.
I have nearly 200 rows in column A:A.
Among this, I need to keep few rows and delete others. My original file is exactly in the same format of attached file.
I want VBA code to delete all the rows between different criterias as mentioned.

Please note:I want cells with criteria values also to be deleted.
Overall,after I run the macro,only rows with "Must not be deleted" must be retained.

Plz don't waste your time in writing code in reverse way (Cells with "Must not be deleted" must remain)
I need a code to key-in my criteria.

Thanks for all your support.

Madvesh

[humdingaling]

Sub Del()
    
    Dim j As Long
    
    For j = Range("A65536").End(xlUp).Row To 1 Step -1
        
        Select Case True
            
            Case Cells(j, 1) = "Must not be deleted"
                'do nothing
                
            Case Cells(j, 1) Like "?riteria*"
                'add random critera
                
            Case Else
            Rows(j).Delete
        End Select
    Next

End Sub

add more cases for more criteria
not quite sure if you wanted criteria to be deleted or not

[mchilapur]

Sub Del()
    
    Dim j As Long
    
    For j = Range("A65536").End(xlUp).Row To 1 Step -1
        
        Select Case Cells(j, 1)
            
            Case Is = "Must not be deleted"
                'do nothing
                
            Case Is = "criteria*"
                'add random critera
            
            Case Else
            Rows(j).Delete
        End Select
    Next

End Sub

add more cases for more criteria

Thanks for your quick response.
In your code I can key-in only thoz criterias which has 'criteria' string in it.
But I need code to be more generalised.
In real scenario, I have few statements that I need to key-in.

plz assume that each criteria in my excel file is different random statements and provide me more generic code.

Thanks sir.

[humdingaling]

i think my revised code is very generalised already
you add in what you want to exempt
you will have to add in every criteria in manually because you are being so general about your criteria

[humdingaling]

Sub Del()
    
    Dim j As Long
    
    For j = Range("A65536").End(xlUp).Row To 1 Step -1
        
        Select Case True
            
            Case Cells(j, 1) = "Must not be deleted"
                'do nothing
                
            Case Cells(j, 1) Like "insert*"
                'add random critera
            
            Case Cells(j, 1) Like "whatever*"
                'add random critera
            
            Case Cells(j, 1) Like "you*"
                'add random critera
            
            Case Cells(j, 1) Like "want*"
                'add random critera
            
            Case Cells(j, 1) Like "as*"
                'add random critera
            
            Case Cells(j, 1) Like "?riteria*"
                'add random critera
                           
            Case Else
            Rows(j).Delete
        End Select
    Next

End Sub

[mchilapur]

No no, your code basically works in reverse way...Means, it retains the rows that I keyin...But I want rows to deleted between each set of criteria.
That criteria are rondom statements and are constant.

[humdingaling]

to write a code like that you would need all the criteria in the fast place
if you have all the criteria in place in the first place then the code i posted works the same way?

[mchilapur]

Like rows from criteria1 and criteria2 must be deleted. I want to keyin "criteria1" and "criteria2"..Similarly for criteria3 and 4, 5 and 6 so on...
Thats the reason I have color coded them accordingly.

[humdingaling]

Sub Del()
    
    Dim j As Long
    Dim x As Long
    
    x = Range("A65536").End(xlUp).Row
    
    For j = Range("A65536").End(xlUp).Row To 1 Step -1
        
        Select Case True
            
            Case Cells(j, 1) Like "?riteria*"
                
                
                If MsgBox("Delete? " & Cells(j, 1), vbYesNo) = vbYes Then
                    Rows(j & ":" & x - 1).Delete
                    
                End If
                
                x = j
            
        End Select
    Next

End Sub

still not sure exactly what you want
is this closer?

[mchilapur]

Dear humdingaling,
Let me explain my requirement.I feel sorry for wasting your time.
In my excel sheet,rows with 'Must not be deleted' actually represent many random statements and they keep on varying every time.So I dont need this criteria into code.
But I have criteria1 to 6 which are constant.So from each set of criteria (1 to 2, 3 to 4, 5 to 6) I want rows to be deleted (including the criteria rows and between these each set of criteria).

So you ignore "Must not deleted", I have added to just tell that it must not be deleted.
let me know if I am not clear.

[Trebor76]

Hi mchilapur,

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

Option Explicit
Sub Macro2()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If StrConv(Range("A" & lngMyRow), vbLowerCase) <> "must not be deleted" Then Rows(lngMyRow).EntireRow.Delete
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

[humdingaling]

Sub Delv2()
    
    Dim j As Long
    Dim x As Long
    
    x = Range("A65536").End(xlUp).Row
    
    For j = Range("A65536").End(xlUp).Row To 1 Step -1
        
        Select Case True
            
            Case Cells(j, 1) Like "?riteria1"
                Rows(j & ":" & x).Delete
                x = j

            Case Cells(j, 1) Like "?riteria2"
                'Rows(j & ":" & x).Delete
                x = j
        
            Case Cells(j, 1) Like "?riteria3"
                Rows(j & ":" & x).Delete
                x = j
            
            Case Cells(j, 1) Like "?riteria4"
                'Rows(j & ":" & x).Delete
                x = j
            
            Case Cells(j, 1) Like "?riteria5"
                Rows(j & ":" & x).Delete
                x = j
            
            Case Cells(j, 1) Like "?riteria6"
                'Rows(j & ":" & x).Delete
                x = j
                
        End Select
        
        
    Next

End Sub

revised code
i dont know how you are going to achieve "criteria" without putting it into the code itself unless criteria is always the same row number?

[Trebor76]

Hi humdingaling,

Just be wary of setting your last row variable like you do via this line of code...

Range("A65536").End(xlUp).Row

...because (as you probably know) Excel 2007 increased this limit by a multiple of 16 to give each tab a whooping 1,048,576 rows. Therefore if Col. A just happened to have 65,537 (or more rows) your code will set the x variable (in your code above) incorrectly.

Robert

[humdingaling]

yeah i am aware
i should update my template codes to the new one, i'm stuck in the past and showing my age

anyway it seems i am having trouble deciphering what the OP actually wants to do and judging by your code it doesnt seem to just be me

[Trebor76]

and judging by your code it doesnt seem to just be me

Maybe - we'll have to see when they post back I suppose.

I notice the clouds are rolling in across the harbour - l hope we don't have a repeat of previous weather storms!!

Cheers,

Robert

[humdingaling]

i hope not
i didnt bring my gumboots in today

[mchilapur]

Hello humdingaling,
Your above code works as per my requirement.
Thanks for all your support.

[humdingaling]

not a problem
i had a file to go with it but it didnt upload yesterday for some reason
so i attached it anyway here


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.