?
Solved

Macro If data filtered unfilter otherwise run macro

Posted on 2014-01-15
3
Medium Priority
?
2,076 Views
Last Modified: 2014-01-19
I have the following code which works perfectly but I want to add a small functionality, at the beginning of the macro if any of the collums are filtered I need to first have the macro unfilter the data and then run the following macro as normal. If the data is not filter simply ignore and move on with the macro. I am attaching a copy of my file in the event you need to have an example

I am not sure how to accomplish this

Sub Combined_View()
' Combined_View Macro
'
If Right(Range("H3").Value, 4) = "Rate" Then Exit Sub
        
    Range("H2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Range("H2:H2046").Select
    Selection.Cut Destination:=Range("BT2:BT2046")
    Range("BT2:BT2046").Select
   
    Range("L2:L2046").Select
    
    Selection.Cut Destination:=Range("BU2:BU2046")
    Range("BU2:BU2046").Select
    
    Range("P2:P2046").Select
    
    Selection.Cut Destination:=Range("BV2:BV2046")
    Range("BV2:BV2046").Select
    
    ActiveWindow.SmallScroll ToRight:=8
    Range("T2:T2046").Select
   
    Selection.Cut Destination:=Range("BW2:BW2046")
    Range("BW2:BW2046").Select
    
    ActiveWindow.SmallScroll ToRight:=13
    Range("X2:X2046").Select
    
    Selection.Cut Destination:=Range("BX2:BX2046")
    Range("BX2:BX2046").Select
 
    Range("AB2:AB2046").Select
 
    Selection.Cut Destination:=Range("BY2:BY2046")
    Range("BY2:BY2046").Select
   
    Range("AF2:AF2046").Select
   
    Selection.Cut Destination:=Range("BZ2:BZ2046")
    Range("BZ2:BZ2046").Select
  
    Range("AJ2:AJ2046").Select
  
    Selection.Cut Destination:=Range("CA2:CA2046")
    Range("CA2:CA2046").Select
  
    Range("AN2:AN2046").Select
 
    Selection.Cut Destination:=Range("CB2:CB2046")
    Range("CB2:CB2046").Select
  
    Range("AR2:AR2046").Select
   
    Selection.Cut Destination:=Range("CC2:CC2046")
    Range("CC2:CC2046").Select
 
    Range("AV2:AV2046").Select
   
    Selection.Cut Destination:=Range("CD2:CD2046")
    Range("CD2:CD2046").Select
  
    Range("AZ2:AZ2046").Select
  
    Selection.Cut Destination:=Range("CE2:CE2046")
    Range("CE2:CE2046").Select
   
    Range("I2:I2046").Select
 
    Selection.Cut Destination:=Range("CF2:CF2046")
    Range("CF2:CF2046").Select
  
    Range("M2:M2046").Select
    Selection.Cut Destination:=Range("CG2:CG2046")
    Range("CG2:CG2046").Select
  
    Range("Q2:Q2046").Select
    Selection.Cut Destination:=Range("CH2:CH2046")
    Range("CH2:CH2046").Select
   
    Range("Y2:Y2046").Select
    Selection.Cut Destination:=Range("CI2:CI2046")
    Range("CI2:CI2046").Select
    Selection.Cut Destination:=Range("CJ2:CJ2046")
    Range("CJ2:CJ2046").Select
   
    Range("U2:U2046").Select
    
    Selection.Cut Destination:=Range("CI2:CI2046")
    Range("CI2:CI2046").Select
  
    Range("AC2:AC2046").Select
    Selection.Cut Destination:=Range("CK2:CK2046")
    Range("CK2:CK2046").Select
   
    Range("AG2:AG2046").Select
    Selection.Cut Destination:=Range("CL2:CL2046")
    Range("CL2:CL2046").Select
  
    Range("AK2:AK2046").Select
    Selection.Cut Destination:=Range("CM2:CM2046")
    Range("CM2:CM2046").Select
  
    Range("AO2:AO2046").Select
    Selection.Cut Destination:=Range("CN2:CN2046")
    Range("CN2:CN2046").Select
   
    Range("AS2:AS2046").Select
    Selection.Cut Destination:=Range("CO2:CO2046")
    Range("CO2:CO2046").Select
   
    Range("AW2:AW2046").Select
    Selection.Cut Destination:=Range("CP2:CP2046")
    Range("CP2:CP2046").Select
   
    Range("BA2:BA2046").Select
  
    Selection.Cut Destination:=Range("CQ2:CQ2046")
    Range("CQ2:CQ2046").Select
   
    Range("J2:J2046").Select
    Selection.Cut Destination:=Range("CR2:CR2046")
    Range("CR2:CR2046").Select
   
    Range("N2:N2046").Select
    Selection.Cut Destination:=Range("CS2:CS2046")
    Range("CS2:CS2046").Select
  
    Range("R2:R2046").Select
    Selection.Cut Destination:=Range("CT2:CT2046")
    Range("CT2:CT2046").Select
   
    Range("V2:V2046").Select
    Selection.Cut Destination:=Range("CU2:CU2046")
    Range("CU2:CU2046").Select
  
    Range("Z2:Z2046").Select
    Selection.Cut Destination:=Range("CV2:CV2046")
    Range("CV2:CV2046").Select
  
    Range("AD2:AD2046").Select
    Selection.Cut Destination:=Range("CW2:CW2046")
    Range("CW2:CW2046").Select
   
    Range("AH2:AH2046").Select
    Selection.Cut Destination:=Range("CX2:CX2046")
    Range("CX2:CX2046").Select
    
    Range("AL2:AL2046").Select
    Selection.Cut Destination:=Range("CY2:CY2046")
    Range("CY2:CY2046").Select
    
    Range("AP2:AP2046").Select
    Selection.Cut Destination:=Range("CZ2:CZ2046")
    Range("CZ2:CZ2046").Select
   
    Range("AT2:AT2046").Select
    Selection.Cut Destination:=Range("DA2:DA2046")
    Range("DA2:DA2046").Select
   
    Range("AX2:AX2046").Select
    Selection.Cut Destination:=Range("DB2:DB2046")
    Range("DB2:DB2046").Select
   
    Range("BB2:BB2046").Select
    Selection.Cut Destination:=Range("DC2:DC2046")
    Range("DC2:DC2046").Select
    
    Range("K2:K2046").Select
    Selection.Cut Destination:=Range("DD2:DD2046")
    Range("DD2:DD2046").Select
    
    Range("O2:O2046").Select
    Selection.Cut Destination:=Range("DE2:DE2046")
    Range("DE2:DE2046").Select
   
    Range("S2:S2046").Select
    Selection.Cut Destination:=Range("DF2:DF2046")
    Range("DF2:DF2046").Select
   
    Range("W2:W2046").Select
    Selection.Cut Destination:=Range("DG2:DG2046")
    Range("DG2:DG2046").Select
    
    Range("AA2:AA2046").Select
    Selection.Cut Destination:=Range("DH2:DH2046")
    Range("DH2:DH2046").Select
    
    Range("AE2:AE2046").Select
    Selection.Cut Destination:=Range("DI2:DI2046")
    Range("DI2:DI2046").Select
    
    Range("AI2:AI2046").Select
    Selection.Cut Destination:=Range("DJ2:DJ2046")
    Range("DJ2:DJ2046").Select
    
    Range("AM2:AM2046").Select
    Selection.Cut Destination:=Range("DK2:DK2046")
    Range("DK2:DK2046").Select
    
    Range("AQ2:AQ2046").Select
    Selection.Cut Destination:=Range("DL2:DL2046")
    Range("DL2:DL2046").Select
   
    Range("AU2:AU2046").Select
    Selection.Cut Destination:=Range("DM2:DM2046")
    Range("DM2:DM2046").Select
   
    Range("AY2:AY2046").Select
    Selection.Cut Destination:=Range("DN2:DN2046")
    Range("DN2:DN2046").Select
   
    Range("BC2:BC2046").Select
    Selection.Cut Destination:=Range("DO2:DO2046")
    Range("DO2:DO2046").Select
  
    Range("CF2:CQ2046").Select
    
    Selection.Cut Destination:=Range("H2:S2046")
    Range("H2:S2046").Select
   
    Range("CR2:DC2046").Select
    Selection.Cut Destination:=Range("T2:AE2046")
    Range("T2:AE2046").Select
   
    Range("DD2:DO2046").Select
    Selection.Cut Destination:=Range("AF2:AQ2046")
    Range("AF2:AQ2046").Select
    
    Range("BT2:CE2046").Select
   
    Selection.Cut Destination:=Range("AR2:BC2046")
    Range("AR2:BC2046").Select
   
    Range("H2").Select
    
    Range("H2,H2:S2,H4:S2046").Select
    Range("H4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("K2021").Select

    Range("T2:AE2").Select
  
    Range("T2:AE2,T4:AE2046").Select
    Range("T4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("AA2019").Select
  
    Range("AF2:AQ2,AF2011:AQ2015").Select
    Range("AF2011").Activate
 
    Range("AF2:AQ2,AF4:AQ2046").Select
    Range("AF4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
 
    Range("AR2:BC2,AR4:BC2046").Select
    Range("AR4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("H2").Select
    
End Sub

Open in new window

test.xlsm
0
Comment
Question by:joeserrone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 39782693
Hi Joe,

You can test for the sheet using autofilter by checking if the worksheet object's .AutoFilter property returns nothing, then to disable it is the same command as turning it on. Putting it together and you get:
If Not ActiveSheet.AutoFilter Is Nothing Then Cells.AutoFilter

Open in new window

I'm going to take a look at your whole macro abnd clean it up for you too, make it run a little faster/more efficient.

Matt
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39782751
Alternatively, you can use

On Error Resume Next
ActiveSheet.ShowAllData

Open in new window

Thanks
Rob H
0
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 39782752
Here is an updated macro for you, I got rid of all the .Select statements and just referenced the ranges directly when moving the columns. I also removed your need for row 2046, and just use however many rows you're actually using. I also am toggling screen updating for a little faster runtime and less flashy screen.
Sub Combined_View()
' Combined_View Macro
'
    If Right(Range("H3").Value, 4) = "Rate" Then Exit Sub
        
    Application.ScreenUpdating = False
        
    If Not ActiveSheet.AutoFilter Is Nothing Then Cells.AutoFilter
    
    Dim Rw As Long
    Rw = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    Range("H2:H" & Rw).Cut Destination:=Range("BT2")
    Range("L2:L" & Rw).Cut Destination:=Range("BU2")
    Range("P2:P" & Rw).Cut Destination:=Range("BV2")
    Range("T2:T" & Rw).Cut Destination:=Range("BW2")
    Range("X2:X" & Rw).Cut Destination:=Range("BX2")
    Range("AB2:AB" & Rw).Cut Destination:=Range("BY2")
    Range("AF2:AF" & Rw).Cut Destination:=Range("BZ2")
    Range("AJ2:AJ" & Rw).Cut Destination:=Range("CA2")
  
    Range("AN2:AN" & Rw).Cut Destination:=Range("CB2")
    Range("AR2:AR" & Rw).Cut Destination:=Range("CC2")
    Range("AV2:AV" & Rw).Cut Destination:=Range("CD2")
    Range("AZ2:AZ" & Rw).Cut Destination:=Range("CE2")
    Range("I2:I" & Rw).Cut Destination:=Range("CF2")
    Range("M2:M" & Rw).Cut Destination:=Range("CG2")
    Range("Q2:Q" & Rw).Cut Destination:=Range("CH2")
    Range("Y2:Y" & Rw).Cut Destination:=Range("CJ2")
    Range("U2:U" & Rw).Cut Destination:=Range("CI2")
    Range("AC2:AC" & Rw).Cut Destination:=Range("CK2")
    Range("AG2:AG" & Rw).Cut Destination:=Range("CL2")
    Range("AK2:AK" & Rw).Cut Destination:=Range("CM2")
    Range("AO2:AO" & Rw).Cut Destination:=Range("CN2")
    Range("AS2:AS" & Rw).Cut Destination:=Range("CO2")
    Range("AW2:AW" & Rw).Cut Destination:=Range("CP2")
    Range("BA2:BA" & Rw).Cut Destination:=Range("CQ2")
    Range("J2:J" & Rw).Cut Destination:=Range("CR2")
    Range("N2:N" & Rw).Cut Destination:=Range("CS2")
    Range("R2:R" & Rw).Cut Destination:=Range("CT2")
    Range("V2:V" & Rw).Cut Destination:=Range("CU2")
    Range("Z2:Z" & Rw).Cut Destination:=Range("CV2")
    Range("AD2:AD" & Rw).Cut Destination:=Range("CW2")
    Range("AH2:AH" & Rw).Cut Destination:=Range("CX2")
    Range("AL2:AL" & Rw).Cut Destination:=Range("CY2")
    Range("AP2:AP" & Rw).Cut Destination:=Range("CZ2")
    Range("AT2:AT" & Rw).Cut Destination:=Range("DA2")
    Range("AX2:AX" & Rw).Cut Destination:=Range("DB2")
    Range("BB2:BB" & Rw).Cut Destination:=Range("DC2")
    Range("K2:K" & Rw).Cut Destination:=Range("DD2")
    Range("O2:O" & Rw).Cut Destination:=Range("DE2")
    Range("S2:S" & Rw).Cut Destination:=Range("DF2")
    Range("W2:W" & Rw).Cut Destination:=Range("DG2")
    Range("AA2:AA" & Rw).Cut Destination:=Range("DH2")
    Range("AE2:AE" & Rw).Cut Destination:=Range("DI2")
    Range("AI2:AI" & Rw).Cut Destination:=Range("DJ2")
    Range("AM2:AM" & Rw).Cut Destination:=Range("DK2")
    Range("AQ2:AQ" & Rw).Cut Destination:=Range("DL2")
    Range("AU2:AU" & Rw).Cut Destination:=Range("DM2")
    Range("AY2:AY" & Rw).Cut Destination:=Range("DN2")
    Range("BC2:BC" & Rw).Cut Destination:=Range("DO2")
    
    Range("CF2:CQ" & Rw).Cut Destination:=Range("H2")
    Range("CR2:DC" & Rw).Cut Destination:=Range("T2")
    Range("DD2:DO" & Rw).Cut Destination:=Range("AF2")
    Range("BT2:CE" & Rw).Cut Destination:=Range("AR2")
    
    With Range("H2:S2,H4:S" & Rw).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

    With Range("T2:AE2,T4:AE" & Rw).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    With Range("AF2:AQ2,AF4:AQ" & Rw).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
 
    With Range("AR2:BC2,AR4:BC" & Rw).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 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