Set range from one range to another

cbrf23

Board Regular
Joined
Jun 20, 2011
Messages
241
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
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You can just use:
Code:
Set rngClear = Range(rngStart, rngEnd)
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top