Solved

Macro If data filtered unfilter otherwise run macro

Posted on 2014-01-15
3
1,527 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 500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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