VBA Delete Files that are not ending in <insert text here>

[Ottsel]

Fairly new still to VBA. I've tried to put this together, but keep running into issues. Anyone see what I'm doing wrong?

In short, whatever is in column E or column 5, if it doesn't end in ".xls" or ".xlsm" then i want to delete the row.

Sub test123()

Dim y As Long
Dim cellValue As String
 
For y = 2 To 257
    cellValue = Cells(y, 5).Value
    If cellValue = Right(cellValue, 4).Value = ".xls" Or cellValue = Right(cellValue, 5).Value = ".xlsm" Then
        Rows(y).EntireRow.Delete
    End If
Next y
    
End Sub

Tips, tricks or pointers would be appreciated! Thanks in advance!

 

[DanteAmor]

cellValue is not an object it's a string, so you can't use it as an object and use the .Value property

But you can use them directly.

Try:

Sub test123()
  Dim y As Long
  Dim cellValue As String
 
  Application.ScreenUpdating = False
  For y = 257 To 2 Step -1
    cellValue = Cells(y, 5).Value
    If Right(cellValue, 4) = ".xls" Or Right(cellValue, 5) = ".xlsm" Then
      Rows(y).EntireRow.Delete
    End If
  Next y
  Application.ScreenUpdating = True
End Sub

-----

Another way to delete:

Sub test1234()
  ActiveSheet.Range("E1:E257").AutoFilter 1, "*.xls", xlOr, "*.xlsm"
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Ottsel]

cellValue is not an object it's a string, so you can't use it as an object and use the .Value property

But you can use them directly.

Try:

Sub test123()
  Dim y As Long
  Dim cellValue As String
 
  Application.ScreenUpdating = False
  For y = 257 To 2 Step -1
    cellValue = Cells(y, 5).Value
    If Right(cellValue, 4) = ".xls" Or Right(cellValue, 5) = ".xlsm" Then
      Rows(y).EntireRow.Delete
    End If
  Next y
  Application.ScreenUpdating = True
End Sub

-----

Another way to delete:

Sub test1234()
  ActiveSheet.Range("E1:E257").AutoFilter 1, "*.xls", xlOr, "*.xlsm"
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

I went with the first option, but I threw in a Not, so it wouldn't delete the xls or xlsm files, which was an error on my end.

As always, thank you Dante!

If Not Right(cellValue, 4) = ".xls" Or Right(cellValue, 5) = ".xlsm" Then

 

[DanteAmor]

so it wouldn't delete the xls or xlsm file

In that case it should be like this.
That is, one condition is not met and the other condition is not met.

If Not Right(cellValue, 4) = ".xls" And Not Right(cellValue, 5) = ".xlsm" Then

Or this way. The conditions must go between parentheses after the NOT, that is, that one or the other condition is not met.

If Not (Right(cellValue, 4) = ".xls" Or Right(cellValue, 5) = ".xlsm") Then

 

[Ottsel]

In that case it should be like this.
That is, one condition is not met and the other condition is not met.

If Not Right(cellValue, 4) = ".xls" And Not Right(cellValue, 5) = ".xlsm" Then

Or this way. The conditions must go between parentheses after the NOT, that is, that one or the other condition is not met.

If Not (Right(cellValue, 4) = ".xls" Or Right(cellValue, 5) = ".xlsm") Then

oh, good to know. Thanks Dante!