Selecting and deleting rows based on criteria

[mancubus]

I still think this is sufficient for range starting in row 13:

Sub M_snb()
    For Each sh In Sheets
        sh.UsedRange.Columns(1).Offset(12).SpecialCells(4).EntireRow.Delete
        sh.Rows(11).SpecialCells(4).EntireColumn.Delete
    Next
End Sub

i certainly agree with you.

but when it comes to process third party data it may become hell on earth.

last week i was asked to consolidate about 500 email attachments. the files contained web imported data and were emailed by a third party on a daily basis for 2 years. the files had 3 rows of headers with merged rows and columns. many third row headers repeated more than once (under different merged areas). some files had blank row(s) at top, some had blank columns at left. column numbers did not match and changed over time.

if the table structures of the files were the same it would take a few minutes to complete the requirement. i even did not need to write a procedure for that.


i think this is the similar case for this thread.




i think i can adopt your code for workbooks with static First Rows.

user again will select a cell and that cell's row number will be used to offset the used range to selected cell's row.


maybe (user first clicks YES button in MsgBox and selects a cell from row 13 for all worksheets in the workbook):

Sub M_snb()


    Set fRange = Application.InputBox("Please Select the First Row of the Range", "First Row Selection", Type:=8)
    For Each sh In Sheets
        sh.UsedRange.Columns(1).Offset(fRange.Row - 1).SpecialCells(4).EntireRow.Delete
        sh.Rows(fRange.Row - 2).SpecialCells(4).EntireColumn.Delete
        For Each it In Array("NE", "NW", "YH", "EM", "WM", "E", "L", "IL", "OL", "SE", "SW")
            sh.UsedRange.Offset(fRange.Row - 1).Replace it, "=12/0", xlWhole
        Next
        sh.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
    Next

End Sub