advanced filter turns off my advanced filter

luke w

Board Regular
Joined
Oct 23, 2002
Messages
53
I am having trouble maintaining my autofilter feature in my macro. My goal is to sort lots of rows and columns based on a particular input option or checkbox selected. I have either an option box or a checkbox for every column of data I would like to sort. The macro for each box has an autofilter which filters based on "Yes" or "No", then an advanced filter feature which is supposed to select the column of data I would like to copy and paste elsewhere, based on unique data. I hope I haven't confused anyone yet. So when the advanced filter is activated then it disrupts the autofilter. The problem with this is that the user won't be able to further filter the columns of data through the use of the option or check boxes. I think it would be easier to send someone the macro file so that they can look at it's enirity. So if your really interested in helping me out I would be more then happy to email the file...here is a portion of the code I'm having trouble with.

'this filters the bend material
Private Sub chkbend_Click()
Range("S:S").Clear
Range("T:T").Clear
If chkbend.Value = True Then
Sheets("Sheet3").Range("F1").AutoFilter _
Field:=6, _
Criteria1:="y"
Sheets("Sheet3").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Range(Range("R2"), Range("R2").End(xlDown)).Select
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Range(Range("S2"), Range("S2").End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, _
copytorange:=Range("T2"), Unique:=True
Range("T2", Range("T2").End(xlDown)).Select
Set Rg = Selection
Application.CutCopyMode = False
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
Range("T2", Range("T2").End(xlDown)).Select
Set Rg = Selection
With lstbxRESULTS
.ColumnCount = 1
.RowSource = Rg.Address
.ColumnHeads = False
End With
Sheets("Sheet3").Select
Range("A1").Select
End Sub

_________________
Thanks
Luke
This message was edited by luke w on 2002-11-12 18:09
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

kieran

Active Member
Joined
Oct 27, 2002
Messages
428
For what it is worth..

It may be easier if you copied the visible cells form the autofilter to another sheet and did the filter/unique processing on the new list in the new sheet. Excel is probably turning off the autofilter as you are trying to paste a range to T2 where some of the rows are hidden,( ie a non-contiguous range).

You should be able to do this will little addtioanal coding, however be sure that you clear the new shhet each time before you use it.

HIH
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,806
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top