Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
|
|||
|
|||
Thanks alot guys, I appreciate it!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|