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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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