How to jump occulted rows made by a filter and select the first, non occulted row or better cell even tho every row has a specific value?

Question

So this is what i want to do after doing a simple filtering i want to select the first range that appears after the filter ranges, and right now all i am doing is selecting a range inside the first occulted row, is there any manners of jumping the occulted rows?

    Set xBaseCell = Range("A2").CurrentRegion
    Set xUnwantedCell = Range("B1:D1")

    xUnwantedCell.Clear
    With xBaseCell
        .AutoFilter 2, "10", xlTop10Items
        .AutoFilter 8, "28", xlFilterValues
        .AutoFilter 11, "SA LIGHT FABS", xlFilterValues
    End With
    xInputBoxValue = InputBox("Do you want me to clear certain filters and advance to the second stage? Yes or no")
    LCase (xInputBoxValue)
    If xInputBoxValue = "no" Then
       Exit Sub
    Else:
         xBaseCell.AutoFilter 2
    Set xSelectedRange = Range("a2").Offset(1, 8)
    xSelectedRange.Select
    End If

score 0 · Accepted Answer · answered Mar 17 '20 at 14:39

0

set xlSelectedRange = Cells(rows.count, 1).specialcells(xlcelltypevisible).end(xldown).Select

If you are looking for a blank cell this will not work, it will skip to the next used cell.

You may want to qualify your ranges with something like:

with Workbooks("myworkbookpath/myworkbook").sheets("Sheet1")
    set xlSelectedRange = .Cells(.rows.count, 1).specialcells(xlcelltypevisible).end(xldown).Select
end with

Depending on what your end goal is here, it is likely you don't need to select anything. How to Avoid Select.

I am unsure of what the last half of your question title means.

answered Mar 17 '20 at 14:39

Warcupine

4,460
3
15
24

I am sorry but what if i want to select a specific cell inside the filter instead of being it on the first filtered row but on the third or forth and on a different column? i cant just mention it is position because it is variable since it is filter dependent any ideas how, to do that – INGl0R1AM0R1 Mar 18 '20 at 11:38
probably some sort of offset, I have no clue what your criteria is for determining which cell so i can't really answer that. – Warcupine Mar 18 '20 at 11:55
There is a userform with certain suppliers names on it, after he filters the table i want it to copy the information filtered and redirected it to another spreadsheet., the variable with the supplier string is xCBUvaluSTR. She is basically the criteria. Does that help you? Right now i am trying to use some advanced filters i will show you the result when i am finished. – INGl0R1AM0R1 Mar 18 '20 at 12:18
So you want all the visible cells copied over? – Warcupine Mar 18 '20 at 12:20
Not all just 3 actually, i now see advanced filters aint gonna work. – INGl0R1AM0R1 Mar 18 '20 at 12:27

How to jump occulted rows made by a filter and select the first, non occulted row or better cell even tho every row has a specific value?

1 Answers1