Solved

Isolate Rows From Formulas

Posted on 2014-01-04
2
403 Views
Last Modified: 2014-01-04
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.
0
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39756813
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
 
LVL 1

Author Comment

by:tracyms
ID: 39756904
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question