Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    set range instead filter criteria.... (2000 sr-1)

    I use this code to set a range with filter ... but is possible to set the range with cells reference?
    Sub SPOSTA_LINEE_ZERO()

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set WS = Worksheets("L0953")

    If WS.FilterMode Then
    WS.ShowAllData
    End If

    Set RNG1 = WS.AutoFilter.Range

    For SPORT = 2 To Worksheets("GRUPPO").Range("L1").Value

    SPORTELLO = Worksheets("GRUPPO").Range("A" & SPORT).Value

    For SOSP = 2 To Worksheets("GRUPPO").Range("K1").Value
    SOSPESO = Worksheets("GRUPPO").Range("J" & SOSP).Value

    RNG1.AutoFilter Field:=12, Criteria1:=SPORTELLO
    RNG1.AutoFilter Field:=4, Criteria1:=SOSPESO

    Set RNG = WS.AutoFilter.Range

    With WS.AutoFilter.Range
    Set RNG1 = .Columns(12).SpecialCells(xlCellTypeVisible)
    FROWS = RNG1.COUNT
    End With

    TEST_ZERO_LINEE = FROWS

    If TEST_ZERO_LINEE > 1 Then

    SOMMA = 0
    CHIUSI = 0

    For Each CEL In RNG1
    If CEL = SPORTELLO And CEL.Offset(0, -8).Value = SOSPESO Then
    RIGA = CEL.Row
    SOMMA = Range("I" & RIGA) + SOMMA
    If CEL.Offset(0, 14) > "" Then
    CHIUSI = CHIUSI + 1
    End If
    End If
    Next CEL

    If SOMMA = 0 Then
    For Each CEL In RNG1
    If CEL = SPORTELLO And CEL.Offset(0, -8).Value = SOSPESO Then
    RIGA = CEL.Row
    WS.Range("AA" & RIGA) = "ZERO"
    End If
    Next CEL

    If WS.FilterMode Then
    WS.ShowAllData
    End If
    End If
    End If

    Next SOSP

    If WS.FilterMode Then
    WS.ShowAllData
    End If

    Next SPORT

    WS.UsedRange.AutoFilter

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: set range instead filter criteria.... (2000 sr-1)

    I don't understand what you want, and why you want it. If the code works, why change it?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set range instead filter criteria.... (2000 sr

    ok...clear all in this post and use this:

    How to set RNG1 only if in column L = SPORTELLO and column D = SOSPESO...

    remember SPORTELLO and SOSPESO are DIM as STRING...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: set range instead filter criteria.... (2000 sr

    A filter may be the most efficient way to do it, but you could also use code like this:
    <code>
    Dim r As Long
    For r = 1 To WS.Range("A65536").End(xlUp).Row
    If WS.Range("L" & r) = SPORTELLO And WS.Range("D" & r) = SOSPESO Then
    If rng1 Is Nothing Then
    Set rng1 = WS.Range("A" & r & ":P" & r)
    Else
    Set rng1 = Union(rng1, WS.Range("A" & r & ":P" & r))
    End If
    End If
    Next r
    </code>
    In this example, A is the first column you want to include, and P the last one. If you want to start at another row than row 1, change For r = 1 To ... accordingly.
    This will be slow for large datasets.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •