Deselect cells on multiple sheets after unhide

S

Sherry

I have the following code I use to remove the autofilters and hidden rows and
columns within a workbook. It works great. My problem is that after it runs,
the columns that were unhidden remain selected. I have more code later in the
process that won't run because there are cells selected.

Is there an easy snippet of code so that I can select cell "A2" on every
sheet in a workbook at once? I've tried a couple on my own but no luck.
Thanks!
 
S

Sherry

Oops...forgot the code. Sorry.

Sub RemoveFiltersAndHiddenRows()

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets

oWS.AutoFilterMode = False
oWS.UsedRange.Rows.Hidden = False
oWS.UsedRange.Columns.Hidden = False
Next

End Sub
 
J

john

Sherry,
see if this does what you want. Be mindful though that your code will error
if any of the sheets are protected.

Sub RemoveFiltersAndHiddenRows()

Dim oWS As Worksheet

With Application

.EnableEvents = False
.ScreenUpdating = False

End With


For Each oWS In ActiveWorkbook.Sheets

With oWS

.Activate

.AutoFilterMode = False

With .UsedRange

.Rows.Hidden = False
.Columns.Hidden = False

End With

.Range("A2").Select

End With

Next

With Application

.EnableEvents = True
.ScreenUpdating = True

End With

End Sub
 
S

Sherry

Works great! Thanks, John!
--
Thanks,
Sherry


john said:
Sherry,
see if this does what you want. Be mindful though that your code will error
if any of the sheets are protected.

Sub RemoveFiltersAndHiddenRows()

Dim oWS As Worksheet

With Application

.EnableEvents = False
.ScreenUpdating = False

End With


For Each oWS In ActiveWorkbook.Sheets

With oWS

.Activate

.AutoFilterMode = False

With .UsedRange

.Rows.Hidden = False
.Columns.Hidden = False

End With

.Range("A2").Select

End With

Next

With Application

.EnableEvents = True
.ScreenUpdating = True

End With

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