• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofilter with only 6 Digits numbers

Hello Experts.

Aim tired of getting this solutions...tried different ways before posting this.

Wanted to filter "A" column with condition with 6 digits only with VBA as rest of the filter's managed with recording.

My data consists of 78000 rows from which need only starting with 6 digits

Example:


upload_2017-3-25_12-11-26.png

Let me know if you need any further information.

Cheers!
 
Last edited:
Hello Vletm.

Thanks for responding.

Actually the number may start with any number...it may contain 6 or 10 digits or so on...but wanted to filter only 6 digits numbers in filter irrespective of data it contains....It's really strait only 6 digits to filter.

Data is at work...So i just gave an example..

For me below highlighted in green are valid only start with any number must contain 6 digits.

upload_2017-3-19_2-34-50.png
 
Last edited:
@jamesexcel1970... as last time (prev case) I tried to write:
If You write that and think this then You'll get this - not that!
Code:
Sub Do_F6N()
    Application.ScreenUpdating = False
    y = 2
    With ActiveSheet
        Do
            F6N = True
            For x = 1 To 6
                N6 = Asc(Mid(.Cells(y, 1), x, 1))
                If N6 < 48 Or N6 > 57 Then F6N = False
            Next x
            If F6N Then .Cells(y, 1).Interior.ColorIndex = 15
            y = y + 1
        Loop Until .Cells(y, 1) = Empty
    End With
    Application.ScreenUpdating = True
End Sub

You used, for me, so called 'reserved words' with 'own meaning' ... did You?
 
Hello Experts

@vletm your code works perfectly but highlights...But wanted to filter.

@ jindon your code works perfectly but hides which are starts with 6 digits.

What really i wanted is to filter with starts with 6 digits.

Please help.

Cheers!
 
vletm

Thank you so much for the above code..

cam i have same to auto filter rather then with
Private Sub Worksheet_Selection Change(By Val Target As Range)


i just need for autofilter for "A" column with "A"

As iam doing autofilter for "B" column with starting with "A" which is working fine.

So that all filter's can work with one go...thanks.

Cheers!
 
Last edited:
... humm? ...
You really need AutoFilter ... without highlites ...
and
just now You give up :)
Okay ... but just check uploaded file...
 

Attachments

  • JamesExcel1970.xlsb
    42.8 KB · Views: 4
Hello Vletm.

Thanks for your efforts.

As i told you there are other filter which are running with a button which i wanted to included in the same ..

Need to filter with a button click.


Cheers!
 
... and next You need that someone else would click the button?
Now, You have many buttons to click!
 

Attachments

  • JamesExcel1970.xlsb
    53.8 KB · Views: 2
... humm? ...
You really need AutoFilter ... without highlites ...
and
just now You give up :)
Okay ... but just check uploaded file...


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With ActiveSheet
            If Not Target.Font.Bold Then
                MyStr = Empty
                y = 2
                Do
                    F6N = True
                    For x = 1 To 6
                        N6 = Asc(Mid(.Cells(y, 1), x, 1))
                        If N6 < 48 Or N6 > 57 Then F6N = False
                    Next x
                    If F6N Then
                        If MyStr <> Empty Then MyStr = MyStr & ","
                        MyStr = MyStr & .Cells(y, 1)
                    End If
                    y = y + 1
                Loop Until .Cells(y, 1) = Empty
                ary = Split(MyStr, ",")
                If .FilterMode Then .ShowAllData
                .Range("A:A").AutoFilter
                .Range("A:A").AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
            Else
                .Range("A:A").AutoFilter Field:=1
            End If
            Target.Font.Bold = Not Target.Font.Bold
            .Range("B1").Select
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Private Sub A_F6N()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With ActiveSheet
            If Not .Range("A1").Font.Bold Then
                MyStr = Empty
                y = 2
                Do
                    F6N = True
                    For x = 1 To 6
                        N6 = Asc(Mid(.Cells(y, 1), x, 1))
                        If N6 < 48 Or N6 > 57 Then F6N = False
                    Next x
                    If F6N Then
                        If MyStr <> Empty Then MyStr = MyStr & ","
                        MyStr = MyStr & .Cells(y, 1)
                    End If
                    y = y + 1
                Loop Until .Cells(y, 1) = Empty
                ary = Split(MyStr, ",")
                If .FilterMode Then .ShowAllData
                .Range("A:A").AutoFilter
                .Range("A:A").AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
            Else
                .Range("A:A").AutoFilter Field:=1
            End If
            .Range("A1").Font.Bold = Not .Range("A1").Font.Bold
            .Range("B1").Select
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
End Sub


Fantastic! Mr vletm

It works..iam really sorry how come i missed..

it's really highlevel of coding i belive...hard to understand person like me who is new..but works like magic...Thank you so very much.

Please let me know if you can cut down this to simple code...Cheers.

Why so many "Cheers" buttons?:(
 
@jamesexcel1970
... so many buttons,
because You liked those buttons (instead of click "A1") !
... and use LOWER code 'A_F6N' with Your buttons.
... and still it's with 'AutoFilter' as You have used as one 'keyword'.
 
Back
Top