Link to home
Start Free TrialLog in
Avatar of tracyms
tracyms

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tracyms
tracyms

ASKER

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!