Select next row in an autofiltered sheet 2

[cllpmc]

Hi all,
in an excel sheet I have made the following:
A dblclick event shows a userform.
The userform picks up values from cells in the same row where the activecell is (where the dblclick is made).
Now I want to have a commandbutton in the userform that clears the userform and pick up value from the next row.
This is not a problem in a normal sheet, but if the sheet is filtered by autofiltering I can't figure out a code that goes to the next (visible) row.

The shortened code is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
UserForm1.Show
End Sub
-------------
Private Sub UserForm_Initialize()
UserForm1.TextBox1.Value = ActiveCell.EntireRow.Cells(1, 1).Value
etc.etc.
End Sub
-------------
Private Sub CommandButton1_Click()
Unload UserForm1
ActiveCell.Offset(1, 0).Activate
UserForm1.Show
End Sub
--------------

Any ideas for changing the commandbutton1 to go to next row in an autofiltered sheet?
It should be quite easy, or...
Regards Claus

 

[SkipVought]

Hi,

You can use

With ActiveCell
   For Each c In Range(Cells(.Row + 1, .Column), Cells(Cells.Rows.Count, .Column)).SpecialCells(xlCellTypeVisible)
      c.Select
      Exit For
   Next
End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[cllpmc]

Hi Skip,
Im not sure i can se through that code, but it works.
(what type of Dim is c?)
GREAT, Thanks a lot.

Claus

 

[SkipVought]

c is a Range Object

Range(Cells(.Row + 1, .Column), Cells(Cells.Rows.Count, .Column)).SpecialCells(xlCellTypeVisible) is the range from the ActiveCell Next Row to the BOTTOM of the spreadsheet.

SpecialCells(xlCellTypeVisible) LIMITS that range to visible cells only.

So the For Each...Next loop gets the NEXT VISIBLE CELL and EXITS.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Loomah]

Hi
This is a virtually (almost totally!) identical solution to Skip's but as I'd done it I thought I'd post it!

The only difference is there's no loop - other than that, just the same!

With ActiveCell
Range(Cells(.Row + 1, .Column), Cells(Cells.Rows.Count, .Column)) _
    .SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[cllpmc]

Thanks both,

How about going to the previous row ?
(I've made some attempts without luck)

Regards Claus

 

[Loomah]

Hi
Not as nice as the 'activate next row' code but it'll do thee job (I hope!)

Dim lRow As Long
With ActiveCell
    lRow = .Row
    Do
        lRow = lRow - 1
        If lRow = 0 Then lRow = 1
    Loop While Rows(lRow).EntireRow.Hidden = True
Cells(lRow, .Column).Select
End With

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[cllpmc]

It certainly does,

Thanks again, Loomah

Regards claus