Gedefinieerd naam met vba en variabel bereik

[wiki]

Met onderstaande code definieer ik de naam "Inzet1"

Sub inzet1()
'
' inzet1 Macro
' '

'
   Sheets("Diensten").Range("k2").FormulaArray = "=IF(ISERROR(SMALL(IF(R2C1:R81C1=R1C,ROW(R2C1:R81C1)-(ROW(R2C2)-1)),ROW(R[-1]C[-10]))),"""",INDEX(R2C2:R81C2,SMALL(IF(R2C1:R81C1=R1C,ROW(R2C1:R81C1)-(ROW(R2C2)-1)),ROW(R[-1]C[-10]))))"
  Sheets("Diensten").Range("k2").AutoFill Destination:=Range("k2:k100")
   
   With Sheets("Diensten").Range("K2:K100")
    .Copy
   .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End With
        
        Lastrow = Sheets("Diensten").Range("k" & Rows.Count).End(xlUp).Row
        
        Set DataRange = Sheets("Diensten").Range("k2:k" & Lastrow)
        
        ThisWorkbook.Names.Add Name:="Inzet1", RefersTo:="=" & DataRange.Address
 Application.CutCopyMode = False
End Sub

maar door het benoemen van het bereik tot rij 100 blijft de Lastrow rij 100 ook al zijn de cellen leeg. Als ik deze cellen selecteer en Delete dan wordt de lastrow wel de juiste.
Hoe moet ik deze code aanvullen of aanpassen?

 

[Wigi]

Gebruik een uitgebreide filter:

Sub inzet1()

    With Columns(11)
        
        .ClearContents
        Columns(2).AdvancedFilter xlFilterCopy, , .Cells(1), True
        .Sort .Cells(1), Header:=xlYes
        
    End With
    
End Sub

De formule voor het dynamische bereik is dan:

=VERSCHUIVING(Diensten!$K$1;1;;AANTALARG(Diensten!$K:$K)-1)

Wigi

 

[wiki]

Beste Wim,

De formule voor het dynamisch bereik is beter dan ik had, omdat deze altijd eindigt met de eerste lege cel.

In de code ontbreekt volgens mij nog iets. Ik krijg alle codes in kolom 12 terwijl dit alleen de codes met deelcode 1 uit kolom 1 moeten zijn. Wat moet ik aanvullen?

gr wim

 

[Wigi]

Bvb.

Sub inzet1()

    [K1] = [A1]: [K2] = 1
    Columns("A:B").AdvancedFilter xlFilterCopy, [K1:K2], [L1], True
    ThisWorkbook.Names("Inzet1").RefersTo = [M2].Resize(Application.CountA(Range("M:M")) - 1)
    Columns("K:L").Delete
    
End Sub

Het dynamisch bereik wordt nu door de VBA-code gezet.

Wigi

 

[wiki]

I'm impressed. Voorstel voor een nieuwe smiley "petje af".

WiGi bedankt

gr wim