Offset to a visible cell .... without a loop !! xl 2003

[joe.muckle]

Hi guru's

Does anyone know how to offset to a visible cell without using a loop??

I currently use the code:

Do
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.EntireRow.Hidden = False

I need to do this without the loop as my worksheet is getting to around 20,000 rows and the macro takes a long time to run !!

Any ideas ??

Thanks in advance,
Joe.

 

[MrKowz]

Try:

Public Sub SelectFirstVisibleCell()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).Resize(1, 1).Select
End Sub

 

[joe.muckle]

Sorry, dont think I explained myself to well.

What I am doing is filtering a column (column C) and then offsetting down from Cell C1 to the next visible cell.

The code I entered earlier works but takes too long.

Im afraid ur example didnt work in my 'trial' worksheet. Could this be something to do with my code??

Sub JOE()
Dim LR As Long
Range("C1").Select
Selection.AutoFilter Field:=1, Criteria1:="3"
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C1:C" & LR).SpecialCells(xlCellTypeVisible).Resize(1, 1).Select
End Sub

 

[joe.muckle]

Bump ..... Any ideas ????

 

[MrKowz]

Edit: The code I just posted fails under certain conditions. Give me a few more minutes to play with this.

 

[MrKowz]

Here is what I came up with:

Non-Looping, but complex:

Public Sub Non_Looping()
Dim LR          As Long, _
    CellAdd     As String, _
    ColonPos    As Long, _
    CommaPos    As Long
    
Range("C1").AutoFilter Field:=1, Criteria1:="3"
LR = Range("C" & Rows.Count).End(xlUp).row
CellAdd = Range("C2:C" & LR).SpecialCells(xlCellTypeVisible).Address(False, False)
ColonPos = InStr(CellAdd, ":")
CommaPos = InStr(CellAdd, ",")
If ColonPos = 0 Then ColonPos = Len(CellAdd) + 1
If CommaPos = 0 Then CommaPos = Len(CellAdd) + 1
Range(Left(CellAdd, Application.Min(ColonPos, CommaPos) - 1)).Select
End Sub

Looping, but much faster than your loop since it does not "Select":

Public Sub Fast_Looping()
Dim LR  As Long, _
    i   As Long
    
Range("C1").AutoFilter Field:=1, Criteria1:="3"
LR = Range("C" & Rows.Count).End(xlUp).row
For i = 1 To LR
    If Range("C" & i).EntireRow.Hidden = False Then
        Range("C" & i).Select
        Exit For
    End If
Next i
End Sub

 

[joe.muckle]

It works .... sort off !!!!!

It works fine if I filter for the number 3

Sub JOE()
Dim LR      As Long, _
    CellAdd As String
Range("C1").AutoFilter Field:=1, Criteria1:="3"
LR = Range("C" & Rows.Count).End(xlUp).Row
CellAdd = Range("C2:C" & LR).SpecialCells(xlCellTypeVisible).Address(False, False)
Range(Left(CellAdd, InStr(CellAdd, ":") - 1)).Select
ActiveCell.Select

End Sub

but as soon as I change it to filter for the number 2 the macro fails ?????

I cant see why.

The only thing I have noticed is that when I filter for 3 CellAdd = C?,C?:C?
and when I filter for 2 CellAdd = C?,C?,C?

Does the colon ) need to be there??

 

[joe.muckle]

Ignore the last question it now works fine

Thanks for your help.

 

[MrKowz]

It works .... sort off !!!!!

It works fine if I filter for the number 3

Sub JOE()
Dim LR      As Long, _
    CellAdd As String
Range("C1").AutoFilter Field:=1, Criteria1:="3"
LR = Range("C" & Rows.Count).End(xlUp).Row
CellAdd = Range("C2:C" & LR).SpecialCells(xlCellTypeVisible).Address(False, False)
Range(Left(CellAdd, InStr(CellAdd, ":") - 1)).Select
ActiveCell.Select
 
End Sub

but as soon as I change it to filter for the number 2 the macro fails ?????

I cant see why.

The only thing I have noticed is that when I filter for 3 CellAdd = C?,C?:C?
and when I filter for 2 CellAdd = C?,C?,C?

Does the colon ) need to be there??

I hadn't taken into account the three possible conditions for CellAdd:

1. The first cell is a part of a continuous range (eg C4:C6)
2. The first cell is not a part of a continuous range (eg C4,C6:C9 or C4,C6,C9)
3. The first cell is the only cell visible (eg C4)

The newer code I have posted will account for each of these conditions.

 

[joe.muckle]

Perfect.

Thanks for explaining the code aswel !!!!!