PDA

View Full Version : Advance Filter using VBA



Svmaxcel
08-23-2017, 04:30 AM
I have data in column A,b,c its a huge list.
Example :
Sheet 1
Headings.
Let's say column A is System type like Desktop/Laptop.
Column B is System model like ABC/XYZ/PQR.
Column C has part # like 1234/5678/789/345...

Requirements Sheet2.
By default all column A,B,C should have all data as it is in Sheet1.

Now if I click on any System type (column A), it should auto filter the list in Column B and C.
If I click on any specific System Model(column B), Column 3 should display list corresponding to the selection

mdmackillop
08-23-2017, 05:17 AM
Revised for successive filter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 And Target.Row > 4 And Target <> "" Then
Cells(2, Target.Column) = Target
Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1").CurrentRegion, Unique:=False
With Sheets(2)
.Cells.ClearContents
Range("A4").CurrentRegion.Copy .Cells(1, 1)
End With
Else
Range("A2:C2").ClearContents
On Error Resume Next
ActiveSheet.ShowAllData
End If
End Sub

mana
08-23-2017, 05:23 AM
autofilter


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Intersect(Target, Cells(1).CurrentRegion) Is Nothing Then Exit Sub

Sheets("Sheet2").Cells(1).CurrentRegion.ClearContents

With Cells(1).CurrentRegion
.AutoFilter Target.Column, Target.Value
.Copy Sheets("Sheet2").Cells(1)
End With

Cancel = True

End Sub

Svmaxcel
08-24-2017, 12:22 AM
Thanks for help.
I am looking for something like this.
Assume sheet 1 for data
Sheet 2 will have the same column heading and everything will be displayed.
But in sheet 2 if I click on any cell it should filter accordingly.
File attached.

mdmackillop
08-24-2017, 01:07 AM
You've made no comment on the proposed solutions posted.


But in sheet 2 if I click on any cell it should filter accordingly.
As you have sheet names, please use them. I assume Sheet 2 is Filter
There are only headers in sheet Filter in your example so how can this work?

Svmaxcel
08-25-2017, 10:45 AM
The sheet you made was awesome
How ever I want that the raw data should be in sheet 1 and sheet 2 should be the filter.
I mean sheet 1 will be hidden, but users can use sheet2 for filtering data

mdmackillop
08-25-2017, 01:43 PM
Put my code in your Filter sheet code module with layout as per my example.

Svmaxcel
08-26-2017, 04:00 PM
Pasted the code didn't work, can you please help with an example file, if possible.

mana
08-26-2017, 06:45 PM
Filter sheet module


Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Unprotect

If Intersect(Target, Cells(2).CurrentRegion) Is Nothing Then
Sheets("Data").Cells(1).CurrentRegion.Copy Cells(2)
AutoFilterMode = False
Else
Cells(2).CurrentRegion.AutoFilter Target.Column - 1, Target.Value
End If

Cancel = True
Protect

End Sub



マナ

mdmackillop
08-27-2017, 12:53 AM
If you remove row and column headers, make column A narrow and move your data location then not surprisingly it may go unnoticed that the code needs to be amended. For the future, keep your samples basic and fix the appearance once things are working.