Automate deleting rows containing a specific word in a specific column

[tompepper]

Hi,

I have several thousand files where I need to delete rows containing a specific word in a specific column, e.g. if the cell contains the word forecast in column C.
I also need to check if cells in another column contains a number, period and space before a word and if so remove the number, period and space but leave the word, and not delete the row.

Please can someone help me with a macro to do this automatically without needing me to manually open each file.

Thanks

 

[Skyybot]

Okay. Place this in a new Workbook. It will specifically look for the word Forecast. Just like that. With capital F. No other variations. If the Case of the word Forecast changes we will have to look at changing the code. I suggest that you copy a few of the .csv files into a new folder for testing purposes.

Option Base 1
Sub getEditXLS()
Dim wb As Workbook, sht As Worksheet, bRng As Range, nRng As Range, cell As Range
Dim fPath As String, foldObj, fold, fileObj, file
Application.ScreenUpdating = False
Application.DisplayAlerts = False
fPath = Application.GetSaveAsFilename(Title:="Select first .csv file")
fPath = Left(fPath, InStrRev(fPath, "\") - 1)
Set foldObj = CreateObject("Scripting.FileSystemObject")
Set fold = foldObj.GetFolder(fPath)
Set fileObj = fold.Files
For Each file In fileObj
    Workbooks.Open (fPath & "\" & file.Name)
    Set wb = Workbooks(file.Name)
    Set sht = wb.Worksheets(1)
    Set bRng = sht.Range(Cells(1, 2), Cells(sht.UsedRange.Rows.Count, 2))
    For i = bRng.Rows.Count To 1 Step -1
        If Not InStr(1, bRng.Cells(i, 1).Value, "Forecast") = 0 Then
            sht.Rows(i).Delete
        End If
    Next i
    Set nRng = sht.Range(Cells(1, 6), Cells(sht.UsedRange.Rows.Count, 6))
    For Each cell In nRng
        If Not cell.Value = "" Then
            cell.Value = Right(cell.Value, Len(cell.Value) - 3)
        End If
    Next cell
    wb.Save
    wb.Close
Next file
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[jdellasala]

At the risk of getting dinged, I'll say again, if you really have "several thousand files" to process, Power Query is THE way to go. It will be much easier to set up, MUCH easier to maintain and/or modify, more secure, and easiest to refresh if you get more files to process! I also suspect VBA will be slower, but have no way to tell.

 

[tompepper]

I used Power query and got what I needed. Thanks to both who replied for your time and effort.