Results 1 to 1 of 1

Thread: [RESOLVED] Help needed with autofilter column for max value

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018

    Resolved [RESOLVED] Help needed with autofilter column for max value

    I have a macro in vba and facing difficulties to get an autofilter to work for filtering a column for max value.

    I think my issue is that the content of that column. The column originally contains values like: 1.0, 2.0, 3.0 (numbers with a .0 in the end). I have tried e.g. =ISNUMBER(A2) but that returns False for any of the numbers even though I have converted the column to numbers.
    When I manually remove the .0 from those things it works though. So here come my questions:

    - Plan A: is there a way to use autofilter for the max value given my circumstance of that column.
    - plan B: how do I quickly remove the .0 for each cell in that column (some 7000 rows).

    Thanks in advance.

    My code:
        SourceWorkBook.Worksheets(MyWSheets.Name).Range("A2:H" & TRowsSourceFile).AutoFilter Field:=1, _
                     Criteria1:=DMSCCNR(K), Operator:=xlFilterValues  'column 1/A is the document number
                     'filter the higest version
                     SourceWorkBook.Worksheets(MyWSheets.Name).Range("A2:H" & TRowsSourceFile).AutoFilter Field:=3, _
                     Criteria1:=WorksheetFunction.Max(Range("C2:C" & TRowsSourceFile)), Operator:=xlAnd 'column 3/C is the version number
    Edit. OK. I went ahead with plan B and jsut used find .* and replace with nothing. It does the filterign now but I have another problem which I start a new thread on.
    Last edited by Grand; Aug 9th, 2019 at 03:03 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width