PDA

View Full Version : VBA : Help on Filtered Range



Marty
10-24-2016, 12:48 PM
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
10-24-2016, 02:28 PM
Not sure about your data layout on the worksheet, but

Range("O1").CurrentRegion. Select

will select the block of cells surrounding O1

Marty
10-25-2016, 05:58 AM
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
10-25-2016, 08:23 AM
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