Dynamic range in vba

niicommey · Dec 18, 2020

Hello forum,
I am new to VBA and I want to use a command button to clear some ranges. How can I improve the code below such that the range will be dynamic enough to adjust when I delete or insert some new rows.

Code:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("B19:B68") = ""
Sheets("Sheet1").Range("C19:C68") = ""
End Sub

NoS · Dec 18, 2020

Perhaps like this

Code:

Private Sub CommandButton1_Click()
Dim lr As Long      'last row
With Sheets("Sheet1")
    lr = .Range("B:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    .Range("B19:C" & lr).ClearContents
End With
End Sub

p45cal · Dec 19, 2020

Consider adding a condition to the ClearContents line to prevent stuff being deleted above row 19 (for example, if you click the button twice):

Code:

If lr >= 19 Then .Range("B19:C" & lr).ClearContents

Dynamic range in vba

niicommey

New member

NoS

Member

p45cal

Super Moderator