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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.