joeserrone
asked on
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
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
test.xlsm
Alternatively, you can use
Rob H
On Error Resume Next
ActiveSheet.ShowAllData
ThanksRob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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