Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

Determine Which Columns Are Hidden with VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Keith - 07 Mar 2008 18:24 GMT
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 - 07 Mar 2008 18:37 GMT
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.

> 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
[quoted text clipped - 9 lines]
> can
> help.

Signature

Dave Peterson

Keith - 07 Mar 2008 18:59 GMT
> You could build an array that keeps track of columnwidths:
>
[quoted text clipped - 96 lines]
>
> Dave Peterson

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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.