PDA

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

GTO
08-09-2010, 11:38 PM
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

GTO
08-10-2010, 09:44 AM
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

GTO
08-10-2010, 11:29 AM
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?

GTO
08-10-2010, 01:29 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?

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