SOLVED

Delete a table row if a file is not found anymore

Brass Contributor

first of all huge thank you to all the member who helped me in accomplish my project, I m 99%done.

what i need to complete is a way to delete the row from the below table if the the relative file is not found any more in the folder. The text In the column PRODUCT NAME is the same of the file name.

olopa67_0-1648179116421.png

 

5 Replies

@olopa67 

 

Would you please upload the sample file instead of the image?

@Subodh_Tiwari_sktneer  thank you for taking the time to help,

here is the file. 

best response confirmed by olopa67 (Brass Contributor)
Solution

@olopa67 

 

You may try the following code to delete a table row if a recipe card doesn't exist. You may click the button in cell M1 in the attached to run the macro.

 

Sub DeleteTableRowIfFileDoesNotExist()
Dim ws              As Worksheet
Dim tbl             As ListObject
Dim fso             As Object
Dim i               As Long
Dim strFilePath     As String

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set tbl = ws.ListObjects("Table1")
Set fso = CreateObject("Scripting.FileSystemObject")

For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
    strFilePath = tbl.ListColumns("CLICK TO OPEN RECIPE CARD").DataBodyRange.Cells(i).Value
    If Not fso.fileexists(strFilePath) Then
        tbl.ListRows(i).Delete
    End If
Next i

Application.ScreenUpdating = True

End Sub

 

I have no words to express my gratitude Thank you so much it works perfectly

You're welcome @olopa67! Glad I could help.

 

Have a wonderful time ahead!

1 best response

Accepted Solutions
best response confirmed by olopa67 (Brass Contributor)
Solution

@olopa67 

 

You may try the following code to delete a table row if a recipe card doesn't exist. You may click the button in cell M1 in the attached to run the macro.

 

Sub DeleteTableRowIfFileDoesNotExist()
Dim ws              As Worksheet
Dim tbl             As ListObject
Dim fso             As Object
Dim i               As Long
Dim strFilePath     As String

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set tbl = ws.ListObjects("Table1")
Set fso = CreateObject("Scripting.FileSystemObject")

For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
    strFilePath = tbl.ListColumns("CLICK TO OPEN RECIPE CARD").DataBodyRange.Cells(i).Value
    If Not fso.fileexists(strFilePath) Then
        tbl.ListRows(i).Delete
    End If
Next i

Application.ScreenUpdating = True

End Sub

 

View solution in original post