Delete row with a specific word

[mictlantecuhtli]

Hi guys, I trying to start using VBA to automatize some task at work,

I haved found a code that opened all files in a folder, and works pretty well, but now I need the following:

Delete rows 1 to 3 and 6 to 7 mantain rows 4 and 5

Then look for an specific words "MONEDA NACIONAL", on the first column and delete the entire row where those words appear
Then delete column A, J, K ,l M, N, O and P

Sub RunOnAllFilesInFolder()
    Dim folderName As String, eApp As Excel.Application, fileName As String
    Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook
    Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    Set currWb = ActiveWorkbook: Set currWs = ActiveSheet
    
    'Select folder in which all files are stored
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = currWb.Path
    If fDialog.Show = -1 Then
        folderName = fDialog.SelectedItems(1)
    End If
        
    'Create a separate Excel process that is invisibile
    Set eApp = New Excel.Application:  eApp.Visible = False
    
    'Search for all files in folder [replace *.* with your pattern e.g. *.xlsx]
    fileName = Dir(folderName & "\*.xlsx")
    Do While fileName <> ""
        'Update status bar to indicate progress
        Application.StatusBar = "Processing " & folderName & "\" & fileName
        
        Set wb = eApp.Workbooks.Open(folderName & "\" & fileName)
        
        
        
        
        
        wb.Close SaveChanges:=True 'Close opened worbook w/o saving, change as needed
        Debug.Print "Processed " & folderName & "\" & fileName
        fileName = Dir()
    Loop
    eApp.Quit
    Set eApp = Nothing
    'Clear statusbar and notify of macro completion
    Application.StatusBar = ""
    MsgBox "Completed executing macro on all workbooks"
End Sub

The code I haved found, open the files and and closed saving changes, but can't delete what I need,

Pleae can you help me to achieve this task

Thank's in advanced

 

[NdNoviceHlp]

You can trial inserting this code. You will need to change the sheet name to suit. Please save a copy of your wb before trialling the code. HTH. Dave

Dim Cnt As Integer, LastRow As Integer
Set wb = eApp.Workbooks.Open(FolderName & "\" & Filename)
Set ws = wb.Sheets("Sheet1")
With ws
.Rows(7).Delete
.Rows(6).Delete
.Rows(3).Delete
.Rows(2).Delete
.Rows(1).Delete
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = LastRow To 1 Step -1
If UCase(ws.Range("A" & Cnt)) = "MONEDA NACIONAL" Then
ws.Rows(Cnt).Delete
End If
Next Cnt
With ws
'A, J, K ,l M, N, O and P
.Columns("P").Delete
.Columns("o").Delete
.Columns("N").Delete
.Columns("M").Delete
.Columns("L").Delete
.Columns("K").Delete
.Columns("J").Delete
.Columns("A").Delete
End With
wb.Close SaveChanges:=True

ps. Welcome to the Board!

 

[mictlantecuhtli]

Thank´s dude It works great...

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave

 

[mictlantecuhtli]

Hi dude I have a little problem, the code works great but, for each file the first sheet has the same name that the file example: 01-21.xlsx the first sheet name is 01-21 the next file 02-21.xlsx first sheet name is 02-21 and so on. It is possible change the name of each sheet to sheet1 (Hoja1 in spanish) before the rows delete code runs?

Thank's in advanced

 

[Joe4]

Hi dude I have a little problem, the code works great but, for each file the first sheet has the same name that the file example: 01-21.xlsx the first sheet name is 01-21 the next file 02-21.xlsx first sheet name is 02-21 and so on. It is possible change the name of each sheet to sheet1 (Hoja1 in spanish) before the rows delete code runs?

Thank's in advanced

Try changing all references of Sheets("Sheet1") to Sheets(1).
That will pick the first sheet, regardless of name.

So update references like this:

Set ws = wb.Sheets("Sheet1")

to this:

Set ws = wb.Sheets(1)

 

[mictlantecuhtli]

Try changing all references of Sheets("Sheet1") to Sheets(1).
That will pick the first sheet, regardless of name.

So update references like this:

Set ws = wb.Sheets("Sheet1")

to this:

Set ws = wb.Sheets(1)

Thank's dude, it works great