Identify header, filter and fill content

[Nagoo]

Hello All,

I would like to have a macro which finds the header "Blogs" in the input sheet --> Then apply the filter to identify "NA" and Blanks --> Then replace the blanks and "NA" with "No" --> then take out the filter.

Note: Somtimes Header "Blogs" might not be available in the sheet for which macro needs to skip this process and exit.

Thanks for your valuable suggestions

 

[Yongle]

Try this in a copy of your workbook
- amend "Input Sheet"
- as requested code filters on blanks and "NA" (ie the code looks for text "NA" NOT the error #N/A)
- alternative line is included (commented out)

Sub blogs()
    Dim rng As Range, blogs As Range
    Set blogs = Sheets("[B]Input Sheet[/B]").Cells.Find("Blogs", lookat:=xlWhole)
    If blogs Is Nothing Then End
[COLOR=#000080][I]'insert row to guarantee current region correctly set[/I][/COLOR]
    blogs.EntireRow.Insert
    Set rng = blogs.CurrentRegion
    Set rng = blogs.Resize(rng.Rows.Count)
[COLOR=#000080][I]'filter[/I][/COLOR]
    rng.AutoFilter
    rng.AutoFilter Field:=1, Criteria1:="=NA", Operator:=xlOr, Criteria2:="="
         [COLOR=#ff0000]'rng.AutoFilter Field:=1, Criteria1:="=#N/A", Operator:=xlOr, Criteria2:="="[/COLOR]
[COLOR=#000080][I]'amend values[/I][/COLOR]
    rng.SpecialCells(xlCellTypeVisible).Value = "No"
    blogs.Value = "Blogs"
[COLOR=#000080][I]'reset filter and delete previously added row[/I][/COLOR]
    rng.AutoFilter
    blogs.Offset(-1).EntireRow.Delete
End Sub

 

[Nagoo]

Thanks a Lot


It works fine !


Regards
Nagoo

 

[Yongle]

Thanks for feedback