Is there a better way to do this?

G

GoBow777

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
 
J

Jim Cone

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" <[email protected]>
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
 
R

Rick Rothstein

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
 
G

GoBow777

Rick said:
Exactly what is the AutoFilter doing for you in this code? With the
exception of preserving the contents of Row 2 (from Columns B throug
I) and
providing drop down arrows in its cells, it looks like all your code i

doing is this...

ActiveSheet.Range("$B$3:$I$202").ClearContents

Thank you gentlemen for your response, I appreciate it. Obviously I’
not very good with VBA, I recorded the macro and as I said before i
works fine, but as I am still learning, I am just curious; there mus
be a better way.

My goal is to reset any filtered rows and clear the contents from th
range ($B$3:$I$202) and leave the drop down arrows (row 2) intact
 
J

Jim Cone

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top