Re: UserForm: Jump to the next visible cell

From: Gabor G (ggerencs_at_bigpond.net.au)
Date: 04/06/04 

Date: Tue, 06 Apr 2004 13:00:27 GMT

Tom, thanks a lot, the following macro does it all:

Sub JumpToFirstVisible ()
 Set arng1 = Active***.AutoFilter.Range
 Set arng1 = arng1.Offset(1, 0).Resize(arng1.Rows.Count - 1, 1)
 Set arng1 = arng1.SpecialCells(xlVisible)
 'Set aRng1 = aRng1.SpecialCells(xlCellTypeVisible)
 For Each cell In arng1
   Debug.Print Cells.Address
 Next
 arng1(1, 1).Select
End Sub

I don't know the difference between (xlVisible) and (xlCellTypeVisible) but
both lines work OK.

Gabor

"Tom Ogilvy" <twogilvy@msn.com> schrieb im Newsbeitrag
news:106uiotfmod3o23@news.supernews.com...
> See this for help:
>
> http://tinyurl.com/2ewcl
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Gabor G" <ggerencs@bigpond.net.au> wrote in message
> news:Z4Hbc.138364$Wa.136609@news-server.bigpond.net.au...
> > Hi, I'm using German Excel'97.
> >
> > I have Userform, which helps visualising and editing an excel table of
> more
> > thousand records and more than 70 data elements in each record.
Functions
> > like ScrollBar, SpinButton and other good stuff is provided.
> > It works fine, but I have to provide various selections of the excel
> table,
> > for instance colums AR is "Department" and I have macros providing
> > convenient selection of one or the other departments' records. The
problem
> > is when the user clicks the macro button of her/his deparment, the macro
> > selects the respective department's records, however the userform opens
> the
> > first record on the table which is in most cases hidden, due to the
> > selection.
> >
> > I cannot make a code which would open the UserForm filled with the
first
> > visible record on the selected excel table .
> > I also have problems putting a code together for the SpinButton which
> would
> > jump on to the next visible record, jumping over hidden ones.
> >
> > I would appreciate your help.
> >
> > Gabor
> >
> >
>
>