Consulting

Results 1 to 4 of 4

Thread: Automatic Filter

  1. #1

    Question Automatic Filter

    Hello everyone, i'm new to excel vba and have no experience in using any excel vba / macro functions.
    I am seeking advice from you guys

    In "Sheet 1" I have the following data :

    in column A =
    A1 : AAAA
    A2 : BBBB
    A3 : CCCC
    A4 : DDDD
    A5 : EEEE
    A6 : FFFF
    (all of the value in column A are string)

    in column B =
    B1 : 1
    B2 : 0
    B3 :
    B4 : 0
    B5 :
    B6 : 1
    (only integer

    I want to filter those data in "Sheet 2" with the following conditions :
    If column B = 0, then the data on column A will be written in Sheet 2
    so, BBBB and DDDD will be written in Sheet 2

    How to make the vba / macro code if I'm using excel 2010?

    Thank you so much for your help

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Without headers, Filter is more problematical.
    Sub Test()
    Dim r As Range, cel As Range
    Dim i As Long
    Set r = Sheet1.Cells(1, 1).CurrentRegion
    For Each cel In r.Columns(2).Cells
    If cel = 0 And cel <> "" Then
        i = i + 1
        Sheet2.Cells(i, 1) = cel.Offset(, -1)
    End If
    Next cel
    End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    776
    Location
    Sheet2 A1 formula


    =IF(COUNTIF(Sheet1!$B$1:$B$100,0)<ROW(A1),"",INDEX(Sheet1!$A$1:$A$100,SMALL (IF((Sheet1!$B$1:$B$100=0)*(Sheet1!$B$1:$B$100<>""),ROW($A$1:$A$100)),ROW(A 1))))


    Ctrl+Shift+Enter
    and
    Fill down

  4. #4
    Thank you so much sir!
    The code works!

    You have my highest gratitude!

Posting Permissions

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