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

See this for help:

http://tinyurl.com/2ewcl

--
Regards,
Tom Ogilvy



"Gabor G" wrote in message
...
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

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

Sub JumpToFirstVisible ()
Set arng1 = ActiveSheet.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" schrieb im Newsbeitrag
...
See this for help:

http://tinyurl.com/2ewcl

--
Regards,
Tom Ogilvy



"Gabor G" wrote in message
...
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