macro for clearing cells

  • Thread starter Thread starter mocc
  • Start date Start date
M

mocc

would it be possible to have a small macro for clearing cells only if a
certain critera is met? ie (if l37 blank, do nothing. if I37 has text, clear
i36,j36,h36 and so on?

thanks
 
Try the below

Sub test5()
If WorksheetFunction.IsText(Range("I37")) Then _
Range("H36:J36").ClearContents
End Sub

If this post helps click Yes
 
thanks for this. however i should have said i wanted to insert this into an
exsisting macro. i have tried adding it in but keep getting errors. it workes
great on its own.

JM
 
Difficult to say why you get errors after inserting.

Post your current macro so's we can see where to insert Jacob's code.


Gord Dibben MS Excel MVP
 
ok here goes.

Sub Macro15()
'
' Macro15 Macro
' Macro recorded 26/04/2008 by jimmoc
'

'
Range("C5:D5").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("C5:D5") = Date
Range("H7:H15").Select
Selection.Copy
Range("E7:E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F7:G15").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M8:M19").Select
Selection.ClearContents
Selection.ClearContents
Range("B52:M57").Select
Selection.ClearContents
Range("B44:I48").Select
Selection.ClearContents
Range("R47").Select
Selection.Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Range("R51").Select
Application.CutCopyMode = False
Selection.Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
'
Range("C5:D5").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("L47:M47").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3
Range("L51:M51").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=-3
End Sub


thats it. as you can see its for resetting a document so its ready for the
next day.

thanks
 
You can stick the two lines just about anywhere you wish.

Give this a try.

Sub Macro15()

Range("C5:D5") = Date
Range("E7:E15") = Range("H7:H15").Value
Application.CutCopyMode = False
Range("F7:G15,M9:M19,B52:M57,B44:I48").ClearContents

If WorksheetFunction.IsText(Range("I37")) Then _
Range("H36:J36").ClearContents

Range("R47").Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("R51").Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Range("C5:D5,L47:M47,L51:M51").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3

End Sub


Gord
 
Yes it would make a difference since Jacob's code looks for Text only.

Use this instead.

If Range("I37") <> "" Then _
Range("H36:J36").ClearContents


Gord
 
thanks you have tidied this up nicely, however with the added code to clear
the cells i get an error 1004??
 
I don't know why you're getting an error.

All I added was the code for I37

Here is revised code per your request to clear H36:J36 if I37 contains
anything.

Works for me in 2003 and 2007 versions.

Sub Macro15()

Range("C5:D5") = Date
Range("E7:E15") = Range("H7:H15").Value
Application.CutCopyMode = False
Range("F7:G15,M9:M19,B52:M57,B44:I48").ClearContents

If Range("I37") <> "" Then _
Range("H36:J36").ClearContents

Range("R47").Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Range("R51").Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Range("C5:D5,L47:M47,L51:M51").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3

End Sub


Gord
 
Thanks for this, it works a treat. i eventually found the problem. some of
the cells were merged and therfore i was not including them in the clearance.

thanks for your time and patience
 
Don't you just love merged cells<g>

Since xl97 introduced merged cells feature I have seen so many problems with
them that I wonder why they are still included as a feature.

But, good to hear you are sorted out with the macro stuff.

Gord
 
Back
Top