I'm just thinking there is probably a better way to do this.
I have a userform with a text box that allows my users to add notes to a form.
The notes go into a specific area (between two lines) and there can be multiple notes.
To clear all the notes, I'm using find to find the two lines the notes go in between, and then deleting the cells between them
I'm wondering if there is a way to define a range which encompasses all the cells from one set range to another, that is better than the way I'm currently doing it?
Line in question in red
Thanks!
I have a userform with a text box that allows my users to add notes to a form.
The notes go into a specific area (between two lines) and there can be multiple notes.
To clear all the notes, I'm using find to find the two lines the notes go in between, and then deleting the cells between them
I'm wondering if there is a way to define a range which encompasses all the cells from one set range to another, that is better than the way I'm currently doing it?
Line in question in red
Code:
Private Sub bttnClear_click()
Dim rngClear As Range
Dim rngStart As Range
Dim rngEnd As Range
Set rngStart = Cells.Find(What:="TEST LINE START"After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Set rngEnd = Cells.Find(What:="TEST LINE END", After:=rngStart, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Set rngStart = rngStart.Offset(1, 0)
Set rngEnd = rngEnd.Offset(-1, 0)
[COLOR=red]Set rngClear = Range(rngStart.Address & ":" & rngEnd.Address)[/COLOR]
rngClear.Delete shift:=xlUp
rngEnd.Insert sift:=xlDown
End Sub
Thanks!