?
Solved

Macro - Auto Filter

Posted on 2009-02-21
7
Medium Priority
?
1,017 Views
Last Modified: 2012-05-06
Hi Experts,

Here is a code below. Many a times when the macro is run, the list is in a filtered state. I wish a change to it so that it show off all the items in the list, when the code is run, so that it can run properly. Off course, nothing extra is required if the list is not in a filtered state.....

Regards
Kanwaljit
Private Sub CommandButton1_Click()
    Me.Unprotect "123456"
    
    Application.ScreenUpdating = False
    
    Sheets("Copy").Range("CopyArea").Copy Me.Range("CopyToArea")
    Range("A5").FormulaR1C1 = "1"
    Range("A6").FormulaR1C1 = "2"
    Range("A5:A6").AutoFill Destination:=Range("CopytoAreaSr"), Type:=xlFillDefault
    Range("CopytoAreaSr").Select
 
    Application.ScreenUpdating = False
    
    Me.Protect "123456"
 
End Sub

Open in new window

0
Comment
Question by:Kanwaljit Dhunna
  • 3
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23703756
You can add this line in line-5 this will turn of the autofilter of its turn on...
If Worksheets("Copy").AutoFilterMode Then Selection.AutoFilter
I assumed you wanted to check sheets copy for the same...
Saurabh...
0
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 23704075
This is slightly different than Saurabh post, it doesn't remove the autofilter arrows.
Depending on what you are doing, both ways have advantages.
Tom.
Private Sub CommandButton1_Click()
    Me.Unprotect "123456"
    
    Application.ScreenUpdating = False
    If Worksheets("copy").FilterMode Then Worksheets("copy").ShowAllData
    If me.FilterMode Then me.ShowAllData
 
    Sheets("Copy").Range("CopyArea").Copy Me.Range("CopyToArea")
    Range("A5").FormulaR1C1 = "1"
    Range("A6").FormulaR1C1 = "2"
    Range("A5:A6").AutoFill Destination:=Range("CopytoAreaSr"), Type:=xlFillDefault
    Range("CopytoAreaSr").Select
 
    Application.ScreenUpdating = False
    
    Me.Protect "123456"
 
End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Kanwaljit Dhunna
ID: 23704176
Thanks to both of you.......That did the trick...
Regards
Kanwaljit
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23704286
Kanwal,
Well if both of the solutions worked..then dont you think there should be a split out here...??? .. Do you know how to close a question correctly..?? Refer to the following link:-
http://www.experts-exchange.com/help.jsp?hi=407
 
I have ignored the way you have closed the questions in past...but its a high time you should go through the link and study it..about how to close a question...Going forward its going to help you....Let me know if you need any further clarity over things...
Saurabh...
0
 
LVL 9

Author Comment

by:Kanwaljit Dhunna
ID: 23705835
Hi Saurabh,

I gave the points to Tom, because that was exactly what I wanted. I wanted to show all the data for the current list and didn't wanted to turn the filters off. I feel I made that clear in my question itself. But in any case, any experts devoting time to help other does deserve appreciation. So I thanked both of you. You have been doing fantastic job for the past 2 years and I can understand the feelings when the asker doesn't allot points properly. But beleive me Tom's answer solved the problem the way I needed. I live just 220 kms away from you. Will surely come to hug you, if that can cool the tempers.

Best Regards
Kanwaljit
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23705868
Kanwal,
Leave it...No point of taking this discussion forward...You have a good day buddy....
Saurabh...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question