Solved

Macro If data filtered unfilter otherwise run macro

Posted on 2014-01-15
3
1,369 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
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: 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

Suggested Solutions

Title # Comments Views Activity
Everything left of comma in excel 4 44
Need help Creating PowerShell Script 5 52
Excel Calculation 4 46
Having trouble with a VB Script 17 16
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

791 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