Auto filter problems

    Ok Ok Ok let me rephrase the question


    With autofilter


    I need to know what the first visible item is in column A.


    I can see it but I need xl to see it so that I may use the value.


    In real life all I would do to achieve this is select a1 and use the down arrow or enter to reach the first visible record.


    I think I have exhausted all the options associated with the filter so I am guessing I need to do this using select offset or some such.

    I found this doing a search and tried it out and it worked on the little data I had, see if it helps you out


    Sub FindUnfiltered()
    ' Assumes Row 1 of UsedRange is Header (has the AutoFilter)
    With ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
    If .Areas(1).Rows.Count > 1 Then
    ' 2nd Row is visible: activate it
    .Areas(1).Cells(2, 1).Activate
    ElseIf .Areas.Count > 1 Then
    ' 2nd Row is Hidden. Activate 1st subsequent unhidden row.
    .Areas(2).Cells(1, 1).Activate
    Else
    ' Only one visible area which consists of just one row.
    MsgBox "Nothing visible or used beyond 1st row of range."
    End If
    End With
    End Sub

    Thank you sooo much


    I was stalled on that one.


    (10 minutes later)


    It seems to work fine but I am a bit confused by what it is doing.



    I messed with it a bit and kept breaking it.


    (20 minutes later)


    I think I am starting to understand it as I mess with it.


    Area 1 usually is the invisible data unless your filter happens to pull the first record.


    interesting.


    If I am confused straighten me up.


    Thanks again. a happy bad programmer With ideas bigger than his skillz :).

    Now that I think of it I think you may really suck :) You gave me a new Idea which will make me work really hard.


    Arrrrrggggggggggggg!:barf:


    If I sort my data first and name the ranges associated with the areas then I might well be able to eliminate all the copying and pasting. I can associate the areas as ranges directly into my graphs.


    Do you know how I could pull the address out of the AREA(1) thingy and then define them as named ranges

    I do not think a named range will work on filtered data as it would pull in the entire range including invisible rows. However, you could still copy the range filtered range then name that range.

    I'm not sure what you want to do, but the following code will capture the cells in the filtered column 1 not counting the header, so r.cells(1) would be the first visible cell. The range could well have areas, but it can be used in anything that takes disjoint ranges.

    Code
    Dim r As Range
Set r = Range("A2:A" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
MsgBox r.Address

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login