You could build an array that keeps track of columnwidths:
Option Explicit
Sub testme()
Dim myColWidths() As Double
Dim MaxCols As Long
Dim iCol As Long
With Worksheets("sheet1")
MaxCols = .Columns.Count
ReDim myColWidths(1 To MaxCols)
For iCol = 1 To MaxCols
myColWidths(iCol) = .Columns(iCol).ColumnWidth
Next iCol
.Columns.Hidden = False
'do some stuff
For iCol = 1 To MaxCols
.Columns(iCol).ColumnWidth = myColWidths(iCol)
Next iCol
End With
End Sub
As for the autofilter stuff...
I saved this from a Tom Ogilvy post:
====================================
http://j-walk.com/ss/excel/usertips/tip044.htm
to get it to refresh:
=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)
this is one I wrote back in 2000
Here is a user defined function that will display the criteria in a cell:
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range
If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
You could save these settings, do the work, then parse the saved settings and
reapply the criteria.
Keith wrote:
>
> I have a spreadsheet which will be used by individuals to update an
> Access database. The columns are locked, but I do allow them to hide
> columns to make the data more customized. Periodically they will
> refresh their data from the database. In order to avoid forcing them
> to reformat to their preference (and thus encourage them to refresh
> more frequently) I would like to determine which columns they have
> hidden so I can unhide, update/append the new data, then hide the
> columns again. The same information would be advantageous, though
> less so for the autofilter properties. I have tried in vain to look
> at the help files and have not found the correct text with which to
> search in this group to find the answer. Thanks in advance if you
> can
> help.
--
Dave Peterson
Similar Threads
