Automate deleting rows containing a specific word in a specific column

tompepper

New Member
Joined
Mar 19, 2023
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
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
 
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.

VBA Code:
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
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
Solution
I used Power query and got what I needed. Thanks to both who replied for your time and effort.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top