Border met variabele range

[edwin13387]

Goede middag,

Ik heb een formule in excel, welke soms leeg is ("") en soms een waarde.
Nu wil ik van deze range een macro die hier een border om zet.

Met wat knip plak werk de volgende code, welke bijna werkt.
Hij plaatst de goede border etc. maar de EndUp loopt tot de laatste cel met een formule, ook als deze formule een blaco resultaat heeft.
Hoe kan ik er voor zorgen dat deze de laatste cel met waarde pakt?

mvg
Edwin

Private Sub Worksheet_Change(ByVal Target As Range)

    '//Define variables
    Dim LastRow As Integer      'variable to hold last row of data in column A
    Dim LastCol As Integer        'variable to hold last column of data (assuming row 1 contains headers)
    
    '//Only proceed if cell changed is in column E and before row 4
    If Target.Column <> 5 And Target.Row > 4 Then Exit Sub
    
    '//Get the last row and column of data and assign to variable
    LastRow = Worksheets("Klant analyse").Cells(Rows.Count, 11).End(xlUp).Row
    ' LastCol = Worksheets("Klant analyse").Cells(7, Columns.Count).End(xlToLeft).Column
       
    '//Remove any existing borders that might exist
    Worksheets("Klant analyse").Range("K7:N400").Select         '//Change the cell range to suit
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    '//If the LastRow variable is 1 then only the header row exists, therefore no formatting required, so stop execution
    '  If LastRow = 1 Then Exit Sub
    
    '//Set the cell range to add borders to
    Worksheets("Klant analyse").Range(Cells(7, 11), Cells(LastRow, 14)).Select
    
    
    '//Set the left border (thick line)
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThick
    End With
    
    '//Set the top border (thick line)
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThick
    End With
    
    '//Set the bottom border (thick line)
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThick
    End With
    
    '//Set the right border (thick line)
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThick
    End With
    
    '//Set the inside vertical border (thin line)
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    End With
    
    '//Set the inside horizontal border (thin line)
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    End With
    '//Set A1 as the active cell
    Worksheets("Klant analyse").Range("A1").Select

End Sub

 

[Roeland035]

LR = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

Zelf even aanpassen aan jouw code en het zou moeten werken.

 

[edwin13387]

Hallo,

Bedankt, werkt perfect.
Enkel even de colom naar K gezet.

bedankt.

edwin