Hide and Autofilter Macro based on Criteria

I am attempting to create a Macro that will look at cell I3 and if it = "GBP" then it will do the following

Sub HideColumns()
'
' HideColumns Macro
' Macro recorded 9/16/2010 by dmau
'

'
    Range("E:E,G:G,L:L,N:N,P:P").Select
    Range("P1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

and if I3 = "USD" then

Sub USDHide()
'
' USDHide Macro
' Macro recorded 9/16/2010 by dmau
'

'
    ActiveWindow.SmallScroll ToRight:=3
    Range("F:F,H:H,M:M").Select
    Range("M1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("F:F,H:H,M:M,O:O,Q:Q").Select
    Range("Q1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

and if blank it don't do anything

I will admit I used the "Record Macro" to obtain the code shown above. I am unsure how to insert the criteria so that the autofilter macro knows to look to cell I3 and based on whether it is USD or GBP will perform the appropriate macro.

I have gone thru what I believed were similar posts and can't seem to get past this part. Macro Newbie here but learning everyday! I'm sure I'm close just not close enough

Sorry, but:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Hope this worked, sorry about that, first time I did the "code" thing

I copied pasted your code but there seems to be a problem because it won't stop running. I end up having to go to the Task manager and end the program. I suspect the problem to be an open end compile? I have attempted to "fix" the code but because I am such a novice, I appear to be getting it wrong. Do I need to put in a macro for 0 or "" in the Currency Value field? I was hoping that if I3 = 0, then just autofilter Col P first then Col B (Sorry should have stated that in my original question) PS your version looks cleaner than mine. I am trying to learn this foreign language called VBA!

Still looking for help to get this finished up so I can close it out!

Do you mean just:

Sub HideSomeColumns()
   Select Case Range("I3").Value
      Case "GBP"
          HideColumns
      Case "USD"
          USDHide
       Case Else
          ' do nothing
    End Select
End Sub

Yes but that's not the whole code is it? I mean how do I incorporate that logic with the previously written code? I need specific columns hidden based on the value in I3 and then I need Column P is filtered 1st then Column B is filtered. But if you can simplify the code based on these parameters I would really appreciate it. I am trying to understand how VBA works by looking at the relationships cause and effect of the codes written. Thanks for taking the time to teach this Guru wannabe!

Based on your question, that code checks I3 and then calls whichever of your two posted macros is appropriate. I'm not sure what else you want.

Sorry for being so dense here, but I don't know what I am suppose to do with the code you provided. Do I copy/paste after "next i" at the bottom? or is your code replacing the code previously written and if so how do I tell the "code" which columns to hide based on criteria? I feel like I am Japan and I don't have a translator to help figure out what the signs mean when it comes to VBA. I would like to get a book that effectively explains VBA without putting me to sleep or causing my eyes to roll to the back of my head. (I had that problem studying Audit for the CPA exam)

No, my code is in addition to the code you posted. If you run the code I posted, it checks the value of I3 and then runs whichever of your two original routines is appropriate.

At the risk of appearing like an idiot (and apparently when it comes to VBA I am) Could you please show me what you mean. As a hearing impaired individual I rely on visual demonstrations to fully comprehend what someone is saying. I have tried to do what I think you are saying but I keep getting errors

Sub HideColumns_Autofilter()

Dim LR As Long

LR = Cells(Rows.Count, 5).End(xlUp).Row

For i = 5 To LR

    If Cells(3, 9).Value = "USD" Then
     Range("E:E,G:G,L:L,N:N,P:P").Select
    Range("P1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
    End If
Next i

For i = 5 To LR
    If Cells(3, 9).Value = "GBP" Then
    Range("F:F,H:H,M:M,O:O,Q:Q").Select
    Range("Q1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
    End If
Next i

End Sub

Sub HideSomeColumns()
   Select Case Range("I3").Value
      Case "GBP"
          HideColumns
      Case "USD"
          USDHide
       Case Else
          ' do nothing
    End Select
End Sub

And I really do appreciated your time re: this and I am sure this is frustrating from your end.

Why have you altered the original macros? I assumed they already did what you wanted, in which case you just add my code (separate from the two subs you already had) and then run it.

Please see post #5 & #6. JieJenn posted #4 but never responded to my subsequent posts. In the process I was trying to work it out but have been unsuccessful and when I failed to get a response I contacted you (because you chided me for not following the rules, which upon realizing my error I quickly complied) in the hopes of what my recourse in resolving my macro issues were. I really want to learn VBA but this really isn't something you can quickly pick up on the fly, at least I haven't been able to. Microsoft help in this area has been inadequate which is why I always turn to the ExcelForum. Please forgive my inexperience in trying to tackle this macro as I am trying to do multiple things with one macro. Would it be possible for you to help me or is there someone else I would be better off asking? I feel I have taken up so much of your time and I was unaware of the miscommunication gap in trying to resolve this issue. As I am a visual learner I try to take the codes I am given and deconstruct and understand the code but I am not always successful but I am not good with "word" problems as you can see.

Let's go back to the beginning so we are on the same page.

Did your original two macros do everything you wanted each of them to do?

No, it kept running and I had to do a task manager to exit out it. And thanks so much for starting with clean slate!

I thought that happened when you used JieJenn's code? I'm asking about the code you originally posted?

Yes but only if I did 1 at a time. I did a "Record Macro" and pasted the two together but couldn't figure out how to "write" the code to search for the condition which is what led me to this fine forum to request help.

I did it I did it I did it!!!, Ok after realizing you were using my original macros I thought I would give it one more shot and Voila!! I got it to work!! Now that I see how it plays out I am not going to create a button that will "Unhide" All (Added a button to process the macro and tested it and it appears to be working as it should.) For your viewing pleasure here is my code

Sub HideSomeColumns()
   Select Case Range("I3").Value
      Case "GBP"
          HideColumns
      Case "USD"
          USDHide
      Case "0"
          AutofilterOnly
       Case Else
          ' do nothing
    End Select
End Sub
Sub HideColumns()
'
' HideColumns Macro
' Macro recorded 9/16/2010 by dmau
'

'
    Range("E:E,G:G,L:L,N:N,P:P").Select
    Range("P1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

Sub USDHide()
'
' USDHide Macro
' Macro recorded 9/16/2010 by dmau
'

'
    ActiveWindow.SmallScroll ToRight:=3
    Range("F:F,H:H,M:M").Select
    Range("M1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("F:F,H:H,M:M,O:O,Q:Q").Select
    Range("Q1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub
Sub AutofilterOnly()
'
' AutofilterOnly Macro
' Macro recorded 9/22/2010 by dmau
'

'
    ActiveWindow.SmallScroll ToRight:=3
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

:D

OK, well what I was suggesting is you have three macros (your original two and the one I posted) and the one you run (HideSomeColumns) will then run one of the other two if applicable:

Sub HideSomeColumns()
   Select Case Range("I3").Value
      Case "GBP"
          HideColumns
      Case "USD"
          USDHide
       Case Else
          ' do nothing
    End Select
End Sub

Sub HideColumns()
'
' HideColumns Macro
' Macro recorded 9/16/2010 by dmau
'

'
    Range("E:E,G:G,L:L,N:N,P:P").Select
    Range("P1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

Sub USDHide()
'
' USDHide Macro
' Macro recorded 9/16/2010 by dmau
'

'
    Range("F:F,H:H,M:M").Select
    Range("M1").Activate
    Range("F:F,H:H,M:M,O:O,Q:Q").Select
    Range("Q1").Activate
    Selection.EntireColumn.Hidden = True
    Selection.AutoFilter Field:=21, Criteria1:="<>0", Operator:=xlAnd
    Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
End Sub

Does that do what you want?

Looks like I should have refreshed when I got back to my computer! Good work.
(I removed some unnecessary scrolling in my version - you may wish to do the same)

Thanks, I will do that! I really do appreciate your patience and help, it has been to say at the very least, educational! Now I can close this puppy out and mark it resolved!