johnske
10-18-2004, 08:26 PM
Ok, here's the problem...
I have the following procedure that I can easily change to simply "clear contents" so that all values and code are cleared while leaving formatting, conditional formatting, and data validation intact.
It was suggested that it would be more useful if all entered "values" were cleared while leaving the s/s code intact also. I thought I had a simple solution but I was wrong...
The procedure I now have below does the exact reverse of what is wanted....It removes the s/s code, leaving al the hand entered "values" intact.
I've tried variations on the below to get the reverse - and also used a while (the cell value <> "=" clear the cell)/wend procedure - and if/then - and a copy/paste - but still no luck
Any working solution would be appreciated :bink:
Option Explicit
Sub ClearDataBelowThisRow()
Dim Question, StartClear As Integer, xlDataValidation As Validation
Dim Password, Msg, Answer, Confirmation As VbMsgBoxResult
Dim CellFormula, firstAddress
On Error GoTo LastLine
Question = InputBox("What is the last row number you want to KEEP?", _
"Keep rows down to...")
If Question <= 0 Then Exit Sub
StartClear = Question + 1
ActiveSheet.Unprotect
Application.ScreenUpdating = False
ActiveSheet.Rows(StartClear & ":65536").Select
'//confirm
Confirmation = MsgBox("Do you want to remove all borders, cell formatting," _
& vbLf & "conditional formatting and data validation also?", _
vbYesNoCancel, "Confirm Details...")
'//if user chooses no, clear the cell contents only
'<< NOTE: the following clears the s/s formulae but I want the opposite >>
'<< i.e. to clear any 'contents' but leave the formulae intact >>
If Confirmation = vbNo Then
With Selection
Set CellFormula = .Find("=", LookIn:=xlFormulas)
If Not CellFormula Is Nothing Then
firstAddress = CellFormula.Address
Do
CellFormula.ClearContents
Set CellFormula = .FindNext(CellFormula)
Loop While Not CellFormula Is Nothing And _
CellFormula.Address <> firstAddress
End If
End With
Range("A" & StartClear).Select
Else
'//if user chooses yes, delete all
If Confirmation = vbYes Then
Selection.Delete
Range("A" & StartClear).Select
Else
'//if user decides to cancel
If Confirmation = vbCancel Then
Range("A" & StartClear).Select
End If
End If
End If
Range("A" & StartClear).Select
Exit Sub
LastLine: '//an non-integer was entered for 'Question' so quit the sub
End Sub
I have the following procedure that I can easily change to simply "clear contents" so that all values and code are cleared while leaving formatting, conditional formatting, and data validation intact.
It was suggested that it would be more useful if all entered "values" were cleared while leaving the s/s code intact also. I thought I had a simple solution but I was wrong...
The procedure I now have below does the exact reverse of what is wanted....It removes the s/s code, leaving al the hand entered "values" intact.
I've tried variations on the below to get the reverse - and also used a while (the cell value <> "=" clear the cell)/wend procedure - and if/then - and a copy/paste - but still no luck
Any working solution would be appreciated :bink:
Option Explicit
Sub ClearDataBelowThisRow()
Dim Question, StartClear As Integer, xlDataValidation As Validation
Dim Password, Msg, Answer, Confirmation As VbMsgBoxResult
Dim CellFormula, firstAddress
On Error GoTo LastLine
Question = InputBox("What is the last row number you want to KEEP?", _
"Keep rows down to...")
If Question <= 0 Then Exit Sub
StartClear = Question + 1
ActiveSheet.Unprotect
Application.ScreenUpdating = False
ActiveSheet.Rows(StartClear & ":65536").Select
'//confirm
Confirmation = MsgBox("Do you want to remove all borders, cell formatting," _
& vbLf & "conditional formatting and data validation also?", _
vbYesNoCancel, "Confirm Details...")
'//if user chooses no, clear the cell contents only
'<< NOTE: the following clears the s/s formulae but I want the opposite >>
'<< i.e. to clear any 'contents' but leave the formulae intact >>
If Confirmation = vbNo Then
With Selection
Set CellFormula = .Find("=", LookIn:=xlFormulas)
If Not CellFormula Is Nothing Then
firstAddress = CellFormula.Address
Do
CellFormula.ClearContents
Set CellFormula = .FindNext(CellFormula)
Loop While Not CellFormula Is Nothing And _
CellFormula.Address <> firstAddress
End If
End With
Range("A" & StartClear).Select
Else
'//if user chooses yes, delete all
If Confirmation = vbYes Then
Selection.Delete
Range("A" & StartClear).Select
Else
'//if user decides to cancel
If Confirmation = vbCancel Then
Range("A" & StartClear).Select
End If
End If
End If
Range("A" & StartClear).Select
Exit Sub
LastLine: '//an non-integer was entered for 'Question' so quit the sub
End Sub