hi.
try this with copies of your files. not tested.
pls keep in mind that Last Row of range to be processed is determined by the word "Source".
Sub Del_Rows_n_Cols_on_Condition_AllWS_AllWB_Same_Folder()
'http://www.vbaexpress.com/forum/showthread.php?48681-Selecting-and-deleting-rows-based-on-criteria
Dim wb As Workbook, ws As Worksheet
Dim fRange As Range, delRng As Range
Dim FirstRowQ As Variant
Dim i As Long, FR As Long, LR As Long, LC As Long
Dim fName, fPath As String
fPath = "C:\Files\" 'change to suit. include final \
'fPath = "C:\Users\Attila\test\beax\" 'change to suit. include final \
fName = Dir(fPath & "*.xls*")
'Dir function returns a string representing the first file in the specified folder.
'using it without any arguments returns the next file: fName = Dir()
Do While fName <> "" 'start loop
Set wb = Workbooks.Open(fPath & fName)
'open the file. assign it to wb variable
For i = 1 To wb.Worksheets.Count
'if here are any hidden sheets, select the first visible worksheet.
If Worksheets(i).Visible Then
Worksheets(i).Select
Exit For 'when the first visible worksheet is selected exit loop.
End If
Next i
FirstRowQ = MsgBox(wb.Name & vbLf & vbLf & "Is the first row the same in each worksheet?", vbYesNoCancel, "First Row Decision")
'offer user 2 choices (YES, NO) to base the fist row selection method and another (CANCEL) to stop the code execution.
If FirstRowQ = vbYes Then
'user clicked the YES button. the row of selected cell in the next step will be used as First Row in all worksheets.
Set fRange = Application.InputBox("Please Select the First Row of the Range", "First Row Selection", Type:=8)
'make user select the cell whose row number will be used as first row in all worksheets
FR = fRange.Row
'assign selected cell's row number to FR variable
For Each ws In wb.Worksheets
'loop all worksheets in the opened workbook
With ws
If .Visible = True Then
'check if worksheet is visible. if visible continue next line.
LR = .Cells.Find("Source", , , xlPart, xlByRows, xlPrevious).Row - 1
'find the first occurence of string "Source" from bottom-up. return its row number. -1 means 1 row above Source will be Last Row
LC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
'find the first occurence of any string/value from right-left. return its column number as Last Column.
Set delRng = .Range(.Cells(FR, 1), .Cells(LR, LC))
'set the range to delete rows and columns based on condition usng the variables determined above: FR,LR, and LC. 1 is for column A.
delRng.Columns(1).SpecialCells(4).EntireRow.Delete
'determine the blank cells in the first column of the set range and delete their rows.
For Each it In Array("NE", "NW", "YH", "EM", "WM", "E", "L", "IL", "OL", "SE", "SW")
delRng.Cells.Replace it, "=12/0", xlWhole
Next
'loop all cells in the range for all string criteria and if found a match change it to "=12/0" formula to display a formula error in cell.
'it may be "=15/0" or "=0/0", whatever. this is done to use specialcells method to get all cells with formula errors.
delRng.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
'or =
'delRng.SpecialCells(-4123, 16).EntireRow.Delete
'any of the above lines will delete the rows that contain a formula error.
.Rows(FR).SpecialCells(4).EntireColumn.Delete
'determine the blank cells in the first row of the set range and delete their columns.
End If
End With
Next ws
ElseIf FirstRowQ = vbNo Then
'user clicked the NO button. the row of selected cell in each worksheet will be used as First Row in each woksheets separately.
For Each ws In wb.Worksheets
With ws
If .Visible = True Then
.Activate 'in order to select a range in a worksheet that sheet must be selected first.
Set fRange = Application.InputBox("Select the first row in each worksheet", "First Row Selection", Type:=8)
FR = fRange.Row
LR = .Cells.Find("Source", , , xlPart, xlByRows, xlPrevious).Row - 1
LC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Set delRng = .Range(.Cells(FR, 1), .Cells(LR, LC))
delRng.Columns(1).SpecialCells(4).EntireRow.Delete
For Each it In Array("NE", "NW", "YH", "EM", "WM", "E", "L", "IL", "OL", "SE", "SW")
delRng.Cells.Replace it, "=12/0", xlWhole
Next
.Rows(FR).SpecialCells(4).EntireColumn.Delete
End If
End With
Next ws
Else
'user clicked the CANCEL button.
MsgBox "You cancelled the code execution. Quitting...", vbOKOnly, "QUIT"
'inform the user about cancellation.
wb.Close SaveChanges:=False
'close the first workbook without saving.
Exit Sub
'exit procedure.
End If
wb.Close SaveChanges:=True
'save and close the workbook whose rows and columns are deleted
fName = Dir()
'return the next file
Loop
'go back to Do While fName <> "" line to process the next workbook.
End Sub