Isolate Rows From Formulas

I have the following 2 codes assigned to a button in excel. I'd like them to take effect at row 10. Rows 1 through 9 is for users to enter demographic information and should not be afftected by the code:

Adds a row:

Dim rResult As Long
 rResult = MsgBox("This action will copy a row under your selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If rResult = vbNo Then
 Exit Sub
End If

ActiveSheet.Unprotect
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.EntireRow.Copy Range("A" & ActiveCell.Row + 1)
ActiveSheet.Protect


Clears Contents from a Row but keeps formulas:

Dim sResult As Long
 sResult = MsgBox("This action will clear your data on the selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If sResult = vbNo Then
 Exit Sub
End If
ActiveSheet.Unprotect
Dim rStart As Range, rLast As Range, rConstants As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, 42)
 Set rConstants = Nothing

 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If
ActiveSheet.Protect

Thank you.
LVL 1
tracymsAsked:
Who is Participating?
 
KimputerConnect With a Mentor Commented:
In both cases, start the code with

If ActiveCell.Row < 10 Then
    MsgBox "Please don't select row 1 through 9!"
    Exit Sub
End If

Open in new window


It will only display a warning and do NOTHING else. The reasoning behind this, is they clicked the wrong row, so predicting what you think the user meant, is quite risky. Better let the user think clearly, and solve it by letting him click the correct row and clicking the button again.
0
 
tracymsAuthor Commented:
Cool. I actually did something similar:

If ActiveCell.Row >= 10 Then
 Dim sResult As Long
 sResult = MsgBox("This action will clear your data on the selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If sResult = vbNo Then
 Exit Sub
End If
End If...

but I like your idea better. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.