Solved

Macro If data filtered unfilter otherwise run macro

Posted on 2014-01-15
3
1,022 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
  • 2
3 Comments
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now