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