Dynamische Range in VBA

[Stitch!]

Mijn vraag:

Ik ben op zoek naar een manier om een stukje vba code te laten werken op een variable bereik. Mijn code werkt perfect op een "harde" range. Range("B14:Y60")

Echter moet de code meeschalen zodra Y is ingevuld. Als rij 61 bijvoorbeeld is ingevuld, (van B61 tot Y61) dan moet de range in de vba worden aangepast naar (B14:Y61)

Mijn VBA code:

VBScript: filename

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    
    
    On Error Resume Next
    
    Set rng = Intersect(Target, Range("B14:Y60"))

     
    On Error GoTo 0
    
    If Not rng Is Nothing Then
    

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
        
        StrBody = "Cell " & rng.Address(False, False) & _
            " in werkblad '" & Me.Name & "' is aangepast op " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " door " & Environ$("username") & "."
        
        With OutMail
            .To = "[e-mail]"
            .Subject = "Excel aangepast" & ThisWorkbook.FullName
            .Body = StrBody
            .Send
        End With
        
        Set rng = Nothing
        Set OutApp = Nothing
        Set OutMail = Nothing
    
    End If

End Sub

Wat ik al gevonden of geprobeerd heb:

Ik heb de normale dynamic range functie van excel geprobeerd maar dat werkt natuurlijk niet omdat het om een intersect gaat.

Ook heb ik geprobeerd om het bereik als range().Value uit een cell te halen. (="B14:Y"&502-AANTAL.LEGE.CELLEN(Y15:Y502))

Het mooiste is natuurlijk een oplossing volledig in VBA, hoor graag of iemand suggesties heeft! Bedankt alvast.

[Paultje3181]

Dus je wil de Lastrow bepalen en die vervolgens in je range als Range("B14:Y" & Lastrow) hebben?

Google vba Lastrow geeft je prima voorbeelden van bijvoorbeeld Ron Bruin

[Stitch!]

Paultje3181 schreef op donderdag 04 februari 2016 @ 11:03:
Dus je wil de Lastrow bepalen en die vervolgens in je range als Range("B14:Y" & Lastrow) hebben?

Google vba Lastrow geeft je prima voorbeelden van bijvoorbeeld Ron Bruin

Dat was em inderdaad, enorm bedankt! Zat enorm krom te denken blijkbaar. Heb er nog even .Offset(1) aan geplakt en werkt nu perfect. Ook nog .DeleteAfterSubmit = True aan het stukje mail macro toegevoegd en hij is wel af zo.

Nogmaals enorm bedankt, was hier al te lang mee bezig.


Mijn uiteindelijke code is geworden: (voor de geïntereseerde)

VBScript: email wijzigingen Marco

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    
    On Error Resume Next
  ' Nieuwe regel met dank aan Paultje3181 voor het definieren van de laatste ingevulde rij
   LastRow = Range("Y502").End(xlUp).Row
    
    Set rng = Intersect(Target, Range("B14:Y" & LastRow))

     
    On Error GoTo 0
    
    If Not rng Is Nothing Then
    

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
        
        StrBody = "Cell " & rng.Address(False, False) & _
            " in werkblad '" & Me.Name & "' is aangepast op " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " door " & Environ$("username") & "."
        
        With OutMail
            .To = "[e-mail]"
            .Subject = "Excel aangepast" & ThisWorkbook.FullName
            .Body = StrBody
' verwijderd de email na verzending 
            .DeleteAfterSubmit = True
            .Send
            
        End With
        
        Set rng = Nothing
        Set OutApp = Nothing
        Set OutMail = Nothing
    
    End If

End Sub

Dit kan gebruikt worden voor het verzenden van wijzigingen in een document, degene die de wijziging maakt zal hier niet van op de hoogte zijn.

[ Voor 56% gewijzigd door Stitch! op 04-02-2016 16:17 . Reden: uiteindelijke code toegevoegd. ]

[Paultje3181]

En wat gebeurt er als je b503 vult?

Tenzij je zeker weet dat dit niet kan, kun je beter op een kolom de xlup doen.

[Stitch!]

Goed punt! Aangepast.

[DappereDodo]

Waarom zou je niet ActiveSheet.UsedRange.Rows.Count gebruiken?

[Stitch!]

DappereDodo schreef op donderdag 04 februari 2016 @ 20:12:
Waarom zou je niet ActiveSheet.UsedRange.Rows.Count gebruiken?

Omdat de mail niet verzonden moet worden voordat de laatste rij is ingevuld. Macro werkt nu perfect!