Option Explicit
Sub Delete_Blank_Rows()
Dim ws As Worksheet
Dim LR As Long
Dim TrimRng As Range
Application.ScreenUpdating = False
Set ws = Sheets("Sheet1")
With ws
LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
.Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:= _
"=*-----*", Operator:=xlOr, Criteria2:= _
"=*======*"
.Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set TrimRng = .Range("A1:A" & LR)
Call TrimRange(TrimRng)
Set TrimRng = Nothing
Call test
End With
End Sub
Sub TrimRange(ByRef MyRng As Range)
Dim vY 'as Variant
With MyRng
vY = Evaluate("IF(ROW(" & .Address & "),TRIM(" & .Address & "))")
End With
MyRng.Resize(UBound(vY, 1), 1).Value = vY
End Sub
Sub test()
Dim ws As Worksheet
Dim DataRng As Range
Dim CritRng As Range
Dim LR As Long
Dim LC As Long
Application.ScreenUpdating = False
Set ws = Sheets("Sheet1")
With ws
LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
Set DataRng = .Range("A1:A" & LR)
Set CritRng = .Cells(1, LC).Resize(2)
Cells(2, LC).Formula = "=AND(ISNA(MATCH({""C O L U M N N O. "",""*Fe*(Main)*"",""*GUIDING* "",""*CROSS SECTION:* "",""*REQD. STEEL "",""*exceeds maximum limit*"",""** REQUIRED STEEL*""}&""*"",A2,0)))"
With DataRng
.AdvancedFilter xlFilterInPlace, CriteriaRange:=CritRng
.Offset(1, 0).EntireRow.Delete
End With
.ShowAllData
CritRng.EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub