Determine Which Columns Are Hidden with VBA

[Keith]

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]

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]

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.

Spectacular! Works precisely as I needed- thanks again for your help.