Forum Discussion

olopa67's avatar
olopa67
Brass Contributor
Mar 25, 2022
Solved

Delete a table row if a file is not found anymore

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.

 

excel
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Mar 26, 2022

    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

     

5 Replies

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Mar 26, 2022

        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

         

Resources