Vba to Filter data

saurabhlotankar · #1 05-27-2015, 06:31 AM

Hi Guys,

Hope you all are doing good.

I have attached an excel sheet. In column A you will find a code. In one cell there are 3 codes. Important part of that code is first 6 charaters. Last 10 characters is always a date. you can ignore that.
In "sheet2" i have given 2 filter criteria( Validation in cell B2 and C2). I want to create a vbz code in such a way that what ever selection i will do in sheet2, that should act as a filter criteria in sheet1 in reverse order.

For example:
If i go to sheet2 and select "ES" in B2 and ":07:" in C2 then, all rows in Sheet1 where ever "ES:07:" is mentioned should be deselected.

I would be glad if anyone assist me whith any such code. Thanks.

charlesdh · #2 05-27-2015, 09:36 AM

HI,

A suggestion for sheet1 is to have the data in column A in it's own row.
Code wise would be simpler.
I'll look at this and see what I can do. If any other member wants to pitch in that would be fine.

Note: you mentioned "deselect" I think you wanted to select. Also what do you want to do with the selection?

Pecoflyer · #3 05-27-2015, 09:48 AM

Your example does not contain any code . Could you please post it?

saurabhlotankar · #4 05-27-2015, 11:14 PM

Hi Charlesdh,

I mean to say select deselect only. What i mean to say is code should be such that it should select everything apart from what we have provided in sheet2 in drop down.

For example:

If i go to sheet2 and select "ES" in B2 and ":07:" in C2 then, then filter in sheet1 should select all rows excpt the rows which have ES:07: as one of their value. Thats why i said deselect the rows which we have selected in sheet2.

NoSparks · #5 05-28-2015, 10:00 PM

Try this

Code:

Sub DeSelect_SomeThing()
    Dim filt As String
filt = "*" & Sheets("Sheet2").Cells(2, 2).Text & Sheets("Sheet2").Cells(2, 3).Text & "*"
With Sheets("Sheet1")
    .AutoFilterMode = False
    With .UsedRange
        .AutoFilter field:=1, Criteria1:="<>" & filt, Operator:=xlFilterValues
    End With
End With
End Sub

saurabhlotankar · #6 05-29-2015, 06:07 AM

Tht's working perfectly. Thanks a ton bro for your help.

I have posted one more thread in this forum today with name "Process automation", where i need some help in streamlining few staeps with the help of a VBA macro.

It would be great if you could assist me on the same. I somehow need to close this by tomorrow.

Once again thank you so much for ur help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data validation list filter with range defined by OFFSET	Mango123	Excel	4	03-18-2014 02:52 PM
Mail merge with filter using data inputted by user	Tornado70	Mail Merge	6	04-09-2013 07:16 AM
is it possible, filter data come in a2, b2 cell	gsrikanth	Excel	1	07-05-2012 04:08 PM
filter	kwlickt	Excel	1	03-28-2011 11:15 AM
could you help me help me with advanced filter	Nora	Excel	1	05-15-2009 11:10 AM