ExcelBanter - macro for clearing cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - macro for clearing cells (https://www.excelbanter.com/excel-discussion-misc-queries/243049-macro-clearing-cells.html)

macro for clearing cells

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

macro for clearing cells

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"mocc" wrote:

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

macro for clearing cells

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

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"mocc" wrote:

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

macro for clearing cells

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

On Fri, 18 Sep 2009 10:49:02 -0700, mocc
wrote:

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

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"mocc" wrote:

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

macro for clearing cells

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

"Gord Dibben" wrote:

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

On Fri, 18 Sep 2009 10:49:02 -0700, mocc
wrote:

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

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"mocc" wrote:

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

macro for clearing cells

I37 could have anything in it. I.E text or numbers or time, would this make a
difference?

"mocc" wrote:

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

macro for clearing cells

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").ClearConten ts

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

On Fri, 18 Sep 2009 13:52:01 -0700, mocc
wrote:

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

"Gord Dibben" wrote:

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

On Fri, 18 Sep 2009 10:49:02 -0700, mocc
wrote:

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

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria

"mocc" wrote:

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

macro for clearing cells

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

On Fri, 18 Sep 2009 16:39:01 -0700, mocc
wrote:

I37 could have anything in it. I.E text or numbers or time, would this make a
difference?

"mocc" wrote:

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

macro for clearing cells

thanks you have tidied this up nicely, however with the added code to clear
the cells i get an error 1004??

"mocc" wrote:

I37 could have anything in it. I.E text or numbers or time, would this make a
difference?

"mocc" wrote:

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

macro for clearing cells

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").ClearConten ts

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

On Fri, 18 Sep 2009 20:52:01 -0700, mocc
wrote:

thanks you have tidied this up nicely, however with the added code to clear
the cells i get an error 1004??

"mocc" wrote:

I37 could have anything in it. I.E text or numbers or time, would this make a
difference?

"mocc" wrote:

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

macro for clearing cells

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

"mocc" wrote:

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

macro for clearing cells

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

On Sat, 19 Sep 2009 12:54:01 -0700, mocc
wrote:

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

"mocc" wrote:

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