Macro If data filtered unfilter otherwise run macro

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
joeserroneAsked:
Who is Participating?
 
mvidasConnect With a Mentor Commented:
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
 
mvidasCommented:
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
 
Rob HensonFinance AnalystCommented:
Alternatively, you can use

On Error Resume Next
ActiveSheet.ShowAllData

Open in new window

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.