Solved: Delete rows if part of string is not xls, doc, txt or csv

[jolivanes]

In column A in sheet1 I have file names from folders.
I would like to delete all the rows where the extension of the files
is not .xls, .doc, .txt or .csv. In other words, don't delete the excel,
word etc files
I tried with this code but it deletes everything.

Sub DelAllButTheseFiles()
Application.ScreenUpdating = False
With ActiveSheet
        
    Dim lLastRow As Long
    Dim i As Long
    lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lLastRow To 1 Step -1
        
    If Right("A" & i, 4) <> ".xls" _
    Or Right("A" & i, 4) <> ".doc" _
    Or Right("A" & i, 4) <> ".txt" _
    Or Right("A" & i, 4) <> ".csv" Then
    
Rows(i).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

What would be the proper syntax?

Thanks in advance.

Regards
John

[GTO]

Substitute AND for each of the three OR's

[jolivanes]

Hi GTO.

Thank you for your response.
I had tried that previously and tried again but no change.
For some reason it still deletes everything. Columns B, C, D and E also
are filled with stuff but that should not have any bearing on the code.
I have been trying different things now for quite a while but can't get it to work.
One thing I have not tried is running it in Excel 2003 instead of Excel 2007 but I can't see that making any difference at all.

Thanks and Regards
John

[GTO]

In column A in sheet1 I have file names from folders.
I would like to delete all the rows where the extension of the files
is not .xls, .doc, .txt or .csv. In other words, don't delete the excel,
word etc files I tried with this code but it deletes everything. ...

...I had tried that previously and tried again but no change.
For some reason it still deletes everything. Columns B, C, D and E also
are filled with stuff but that should not have any bearing on the code.
I have been trying different things now for quite a while but can't get it to work.
One thing I have not tried is running it in Excel 2003 instead of Excel 2007 but I can't see that making any difference at all...

Hi John,

I am confused about what it is you are trying to do. In your first, I took it that you wanted to delete the entire row if one of the file extensions was not in Col A in the corresponding row. In your second, it seems that maybe you are just wanting to delete the cells in Col A that do not meet criteria.

Can you say which is the goal, and if just the cells, are we looking to shift cells Up or Left?

Mark

[jolivanes]

Hi Mark.
My apologies for the confusion.
No, not just the cells Mark but the whole row.

Regards
John

[GTO]

ACK! My utter bad. Try:

Option Explicit
    
Sub DelAllButTheseFiles()
Dim lLastRow As Long
Dim i As Long
    
    Application.ScreenUpdating = False
    With ActiveSheet
            
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lLastRow To 1 Step -1
            
            If Right(.Cells(i, "A").Value, 4) <> ".xls" _
            And Right(.Cells(i, "A").Value, 4) <> ".doc" _
            And Right(.Cells(i, "A").Value, 4) <> ".txt" _
            And Right(.Cells(i, "A").Value, 4) <> ".csv" Then
        
                Rows(i).Delete
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Hope that helps,

Mark

[jolivanes]

That did it Mark. Thanks a million.
One more question if I may. The code is case sensitive so if some are .Xls or .XLS or .DOC etc, they will be deleted also.
How would I make it non case sensitive.

Thanks again Mark.

Note: I don't have to start a new thread for this do I?

[someboddy]

There are two ways to do it. The first is to use LCase and convert the file extension to lowercase. The second is to use StrComp, with the third argument as vbTextCompare:
[VBA]If StrComp(Right(.Cells(i, "A").Value, 4), ".xls", vbTextCompare)=0 _
And StrComp(Right(.Cells(i, "A").Value, 4), ".doc", vbTextCompare)=0 _
And StrComp(Right(.Cells(i, "A").Value, 4), ".txt", vbTextCompare)=0 _
And StrComp(Right(.Cells(i, "A").Value, 4), ".csv", vbTextCompare)=0 Then[/VBA]


Anyways, you forgot the Office 2007 types: .xlsx, .xlsm, .docx and .docm. Did you leave them out on purpose?

[GTO]

That did it Mark. Thanks a million.
One more question if I may. The code is case sensitive so if some are .Xls or .XLS or .DOC etc, they will be deleted also.
How would I make it non case sensitive.

Thanks again Mark.

Note: I don't have to start a new thread for this do I?

Of course you may; we are here to help each other.

You could include:
[vba]Option Compare Text[/vba]
at the top of the module to disclude case sensitivity. I would not, as this affects the entire module.

While technically, it adds to processing (as there is more to do), I would simply have the code compare 'apples to apples':

Sub DelAllButTheseFiles()
Dim _
lLastRow    As Long, _
i           As Long
    
    Application.ScreenUpdating = False
    With ActiveSheet
    
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lLastRow To 1 Step -1
    
            If LCase(Right(.Cells(i, "A").Value, 4)) <> ".xls" _
            And LCase(Right(.Cells(i, "A").Value, 4)) <> ".doc" _
            And LCase(Right(.Cells(i, "A").Value, 4)) <> ".txt" _
            And LCase(Right(.Cells(i, "A").Value, 4)) <> ".csv" Then
    
                Rows(i).Delete
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Mark

[jolivanes]

Hi Mark.
Thank you very much for your help. Works like a charm. I really appreciate it.


someboddy.
I tried your suggestion, the supplied code, but it did not work for me.
Unfortunately, I don't know why!
I do appreciate your help though. As for the other possible file extensions, no they are not in the list.
Now I have a chance to thank someone for the good time I had in your country on the 2 occasions I was there, Nice people, good food and a tremendous amount of things to see

Thank you and Regards

John

[someboddy]

Ooops!

change the "=0"s to "<>0"s in my code.

oopsie

[jolivanes]

someboddy.

Yes, that works good also. Thank you very much for the alternative.

Thanks and Regards
John