Creating a proprer macro in Excel

[Derek Upson - Pioneer]

sorry for my ignorance. However, below is what I have so far....where would
you suggest putting the code below and do I need to replace any of the words
in the code below with a reference to my existing code?

Sub ClearContents()
'
' ClearContents Macro
' Clear all Fields in Table
'

'
Range("I66").Select
Selection.ClearContents
Range("I77").Select
Selection.ClearContents
Range("I99").Select
Selection.ClearContents
Range("I1010").Select
Selection.ClearContents
Range("I1111").Select
Selection.ClearContents
Range("I1212").Select
Selection.ClearContents
Range("I1313").Select
Selection.ClearContents
Range("U6:X6").Select
Selection.ClearContents
Range("U7:X7").Select
Selection.ClearContents
Range("U52:X52").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=12
Range("Q55:T55").Select
Selection.ClearContents
End Sub

 

[FSt1]

hi
the select statment is not needed in this case so it can be iliminated. also
the small scroll is not needed. in coding the scroll and select are only
good if you are watching the macro to stuff in design mode. in run mode
usually, things are happening too fast.
Range("I66").ClearContents
Range("I77").ClearContents
Range("I99").ClearContents
Range("I1010").ClearContents
Range("I1111").ClearContents
Range("I1212").ClearContents
Range("I1313").ClearContents
Range("U6:X6").ClearContents
Range("U7:X7").ClearContents
Range("U52:X52").ClearContents
Range("Q55:T55").ClearContents

regards
FSt1

 

[Derek Upson - Pioneer]

Thanks FSt1. I will make those changes right now (great suggestions and I
understand them completely). However, what code would I add if I want a
pop-up box to verify the clearcontents before it is actually done. I am
trying to prevent someone from wiping out work accidentally that took 2
hours to input.

 

[Gord Dibben]

The macro recorder adds a great bunch of "Selects" you don't need.


Sub ClearContents()
Dim rcell as Range
msg = "Are you sure you want to clear range?"
response = MsgBox(msg, vbOKCancel)
If response = vbOK Then
MsgBox "You pressed OK. Cells will be cleared"
For Each rcell In _
ActiveSheet.Range("I67, I913, U6:X7, U52:X52, Q55:T55")
rcell.ClearContents
Next
Else
MsgBox "You pressed cancel. Nothing changed"
Exit Sub

End If
End Sub


Gord Dibben MS Excel MVP

 

[JP]

You could condense it down even further like this

Range("I67").ClearContents
Range("I913").ClearContents
Range("U6:X7").ClearContents
Range("U52:X52").ClearContents
Range("Q55:T55").ClearContents


--JP

 

[Derek Upson - Pioneer]

thanks for the first attempt. It looks like this is a problem since some of
the cells are partially merged? it is giving me a problem. Any
suggestions?

 

[Gord Dibben]

What is a "parially" merged cell?

Cells are merged or not.

Suggestions...................GET RID OF MERGED CELLS!

Note the caps which denotes shouting really loudly.


Gord

 

[Gord Dibben]

And even further like this..........


For Each rcell In _
ActiveSheet.Range("I67, I913, U6:X7, U52:X52, Q55:T55")
rcell.ClearContents


Gord