none
VBA Code RRS feed

  • Question

  • Hi Team,

    Please find below attached image and code,

    I have applied auto-filter in vba ,when it generate its selects only old data , rest it will not add ,after adding new data

    I need only field data and remove #N/A for auto filter 

    NAME

    Contact No

    Email ID

    ID

    Value

    Email

    Valuee

    jay pitroda

    9933322246

    jaypitro08@gmail.com

    #N/A

    #N/A

    #N/A

    #N/A

    robs9112002

    9436420354

    robs92002@yahoo.co.in

    #N/A

    #N/A

    #N/A

    #N/A

    rangasamy

    9794441491

    rangasy191@gmail.com

    #N/A

    #N/A

    #N/A

    #N/A

    nikhil surve

    7724472394

    nikhilse955@gamil.com

    #N/A

    #N/A

    #N/A

    #N/A

    Range("D1").Select

        Selection.AutoFilter
        Range("F1").Select
        ActiveSheet.Range("$A$1:$G$3000").AutoFilter Field:=6, Criteria1:=Array( _
            "164997", "165030", "165039", "165051", "165066", "165149", "165160", "165187", _
            "165224", "165232", "165287", "165312", "165313", "165321", "165391", "165396", _
            "165407", "165415", "165492", "165571", "165603", "165673", "165707", "165778", _
            "165783", "165804", "165806", "165824", "165998", "166143", "166178", "166198", _
            "166260", "166267", "166290", "166308", "166316", "166339", "166344", "166448", _
            "166461", "166479", "166549", "166596", "166625", "166647"), Operator:= _
            xlFilterValues
        Range("F1").Select
    End Sub

    Monday, June 19, 2017 12:58 PM

All replies

  • Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    Andreas.

    Tuesday, June 20, 2017 10:21 AM
  • Hi Harsha_1804,

    Do you mean you add a new row at the last row, but the new row could not be selected? Does the row match the filter criteria? If you create a new worksheet and set auto filter manually, will this problem persists? What's the value of #N/A, is it just a string or an error result of a formula? You could using isna function to confirm if it is an error result. If the value is a string, you could set its value directly. If the value is an error result, I suggest you use IfNa to reset the cell formula for saving it’s formula and removing #N/A at the same time. Besides, I also suggest you share a simple file for testing. Here is the code example.

    Dim cl As Range, rng As Range
        Set rng = ActiveSheet.Range("$A$1:$G$5")
        For Each cl In rng.SpecialCells(xlCellTypeVisible).Rows
            'if cl.Cells(1, 4).value="#N/A" then
            'cl.Cells(1, 4).Value = ""
            'End If
            If Application.WorksheetFunction.IsNA(cl.Cells(1, 4)) Then
            Dim str As String
            str = cl.Cells(1, 4).Formula
            str = Mid(str, 2)
            cl.Cells(1, 4).Formula = "=ifna(" & str & ",0)"
            End If
        Next cl

    Best Regards,

    Terry

    Wednesday, June 21, 2017 4:10 AM