Delete a Variable Range of Rows

Chance0066

New Member
Joined
Sep 11, 2015
Messages
2
So I have scoured the internet to put together this small macro, but I haven't had any luck figuring out what I'm doing wrong. I know the answer is going to be simple, but it certainly beyond me. The goal is to have a checkbox insert several rows from a separate sheet, then delete those same rows if you uncheck the box. The copy and insert functions are working perfectly, but I do not know how to write the line for the range of rows to delete. The Code below is obviously not correct, because rngD is specified for testing. rngD should be the 7 rows below the check box, or maybe there is a better way to do it. Thanks a lot for checking this out, and I'm open to any constructive criticism.

Code:
Sub Insert_LL()


Dim ws As Worksheet
Dim chkB As CheckBox
Dim rngA As Range
Dim rngD As Range
Dim lRow As Long
Dim lRowD As Long
Dim llRows As Range


Set llRows = Range("Lesson_Learned")
Set ws = ActiveSheet
Set chkB = ws.CheckBoxes(Application.Caller)
lRow = chkB.TopLeftCell.Offset(1).Row
lRowD = chkB.TopLeftCell.Offset(7).Row
Set rngA = ws.Rows(lRow)
Set rngD = ws.Rows("18:24") 'needs to specify the range of rows dependant where the checkbox is located'


Application.ScreenUpdating = False


    Select Case chkB.Value
        Case 1
            llRows.Copy
            rngA.Insert Shift:=xlDown
        Case Else
            rngD.Delete
    End Select
            
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
This question was answered on another forum for me. In case anyone else runs across this problem I was advised to alter the Set rngD to
Code:
ws.Rows(ws.Rows("lRow"), ws.Rows('lRowD"))
Worked perfectly.
 
Upvote 0

Forum statistics

Threads
1,215,679
Messages
6,126,180
Members
449,296
Latest member
tinneytwin

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