View Full Version : Solved: Delete rows if part of string is not xls, doc, txt or csv
jolivanes
08-09-2010, 10:58 PM
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
Substitute AND for each of the three OR's
jolivanes
08-10-2010, 09:01 AM
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
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
08-10-2010, 09:58 AM
Hi Mark.
My apologies for the confusion.
No, not just the cells Mark but the whole row.
Regards
John
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
08-10-2010, 12:20 PM
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
08-10-2010, 01:27 PM
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:
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
Anyways, you forgot the Office 2007 types: .xlsx, .xlsm, .docx and .docm. Did you leave them out on purpose?
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:
Option Compare Text
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
08-10-2010, 02:22 PM
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
08-10-2010, 08:27 PM
Ooops!
change the "=0"s to "<>0"s in my code.
oopsie
jolivanes
08-10-2010, 10:18 PM
someboddy.
Yes, that works good also. Thank you very much for the alternative.
Thanks and Regards
John
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.