VBA : Help on Filtered Range

[Marty]

Hi,
I would need your expertise. In my macro, i am filtering by a fix range, but this range can change from another file that could be for instance 7600 lines instead of 7491 lines

Is there a formula that I can write for the FILTER to filter until end of a selection, so if the quantity of line changes all the time i can have a dynamic and not static range ?

Example :

Range("O7491").Select
Range(Selection, Selection.End(xlUp)).Select

**this range could be more or less than 7491.

Any help is appreciated as always,

thanks
Marty.

[Paul_Hossler]

Not sure about your data layout on the worksheet, but

Range("O1").CurrentRegion. Select

will select the block of cells surrounding O1

[Marty]

Paul, yes this is part of the solution i was looking for, but it is more on the level of the FILTER.

This formula for the filter is doing a selection of C1 to C12, but for my needs, when filtering, it could be more and less than 12 lines. Below formula is doing a selection of 12 lines, but would need the macro to do a selection based on the available lines of the selected columns that could be more and less that 12.

Selection.AutoFilter
ActiveSheet.Range("$C$1:$C$12").AutoFilter Field:=1

Would you have an idea of the solution ?

I appreciate your help.

[Paul_Hossler]

Guess I'm not understanding the 'Selection' and filtering on a single column, with Field=1

I usually do it something like this

Option Explicit

Sub Guess_01()
    Dim r As Range
    If ActiveSheet.AutoFilterMode Then ActiveSheet.FilterMode = False
    
    Set r = ActiveCell.EntireColumn.Cells(1, 1)
    Set r = Range(r, r.End(xlDown))
    
    r.AutoFilter
End Sub

Can you post an example WB showing what you're looking to do