PDA

View Full Version : [SOLVED] Automatic Filter



thaofe051291
04-04-2017, 11:41 PM
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

mdmackillop
04-05-2017, 01:57 AM
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

mana
04-05-2017, 05:05 AM
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$1 00<>""),ROW($A$1:$A$100)),ROW(A1))))


Ctrl+Shift+Enter
and
Fill down

thaofe051291
04-05-2017, 08:51 PM
Thank you so much sir!
The code works!

You have my highest gratitude!