Is there a better way to do this?
The macro below works fine but I was wondering is there a better way of writing this without having to write 8 line items?
Sub ClearSheet2() Application.ScreenUpdating = False ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=1 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=2 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=3 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=4 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=5 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=6 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=7 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=8 ActiveSheet.Range("$B$3:$I$202").Select Selection.ClearContents Range("A1").Select End Sub Matt |
Is there a better way to do this?
Sub ClearSheet2()
ActiveSheet.Range("$B$2:$I$202").AutoFilter ActiveSheet.Range("$B$3:$I$202").ClearContents Range("A1").Select End Sub -- Jim Cone Portland, Oregon USA "GoBow777" wrote in message ... The macro below works fine but I was wondering is there a better way of writing this without having to write 8 line items? Sub ClearSheet2() Application.ScreenUpdating = False ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=1 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=2 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=3 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=4 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=5 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=6 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=7 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=8 ActiveSheet.Range("$B$3:$I$202").Select Selection.ClearContents Range("A1").Select End Sub Matt -- GoBow777 |
Is there a better way to do this?
Exactly what is the AutoFilter doing for you in this code? With the
exception of preserving the contents of Row 2 (from Columns B through I) and providing drop down arrows in its cells, it looks like all your code is doing is this... ActiveSheet.Range("$B$3:$I$202").ClearContents -- Rick (MVP - Excel) "GoBow777" wrote in message ... The macro below works fine but I was wondering is there a better way of writing this without having to write 8 line items? Sub ClearSheet2() Application.ScreenUpdating = False ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=1 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=2 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=3 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=4 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=5 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=6 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=7 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=8 ActiveSheet.Range("$B$3:$I$202").Select Selection.ClearContents Range("A1").Select End Sub Matt -- GoBow777 |
Quote:
My goal is to reset any filtered rows and clear the contents from the range ($B$3:$I$202) and leave the drop down arrows (row 2) intact. |
Is there a better way to do this?
If that is really what you want, then go with Jim's code.
-- Rick (MVP - Excel) "GoBow777" wrote in message ... Rick Rothstein;870359 Wrote: Exactly what is the AutoFilter doing for you in this code? With the exception of preserving the contents of Row 2 (from Columns B through I) and providing drop down arrows in its cells, it looks like all your code is doing is this... ActiveSheet.Range("$B$3:$I$202").ClearContents -- Rick (MVP - Excel) "GoBow777" wrote in message ...- The macro below works fine but I was wondering is there a better way of writing this without having to write 8 line items? Sub ClearSheet2() Application.ScreenUpdating = False ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=1 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=2 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=3 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=4 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=5 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=6 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=7 ActiveSheet.Range("$B$2:$I$202").AutoFilter Field:=8 ActiveSheet.Range("$B$3:$I$202").Select Selection.ClearContents Range("A1").Select End Sub Matt -- GoBow777 - Thank you gentlemen for your response, I appreciate it. Obviously I'm not very good with VBA, I recorded the macro and as I said before it works fine, but as I am still learning, I am just curious; there must be a better way. My goal is to reset any filtered rows and clear the contents from the range ($B$3:$I$202) and leave the drop down arrows (row 2) intact. -- GoBow777 |
Is there a better way to do this?
Since .AutoFilter is actually an on/off switch consider this...
'-- Sub JustInCaseTheyComeBack() If ActiveSheet.AutoFilterMode Then 'has filter arrows displayed If ActiveSheet.FilterMode Then 'something is filtered ActiveSheet.Range("$B$2:$I$2").AutoFilter 'remove ActiveSheet.Range("$B$3:$I$202").ClearContents ActiveSheet.Range("$B$2:$I$2").AutoFilter 'add autofilter Else ActiveSheet.Range("$B$3:$I$202").ClearContents End If Else ActiveSheet.Range("$B$2:$I$2").AutoFilter End If End Sub -- Jim Cone Portland, Oregon USA |
Thanks alot guys, I appreciate it!
|
All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com