Tags: cell, clear, column, columns, contain, contents, dates, excel, macro, microsoft, msdn, sheet, software

MACRO TO CLEAR CELL CONTENTS

On Microsoft » Microsoft Excel

2,922 words with 2 Comments; publish: Fri, 23 May 2008 09:05:00 GMT; (3061,565.43, « »)

I have an Excel sheet on which:

1. columns B and D contain the same dates

down each column.

2. From column E and up contain data

** I am looking for a macro that will loop through

columns B and D. If it finds any row on which the

dates in B and D do not fall within the last month's

dates then

- it should clear those dates in B and D, and also

- clear all data on that row starting from Column E and

up.

- Where B and D do not contain any dates it

should do nothing.

I would appreciate your assistance very

much. Thanks.

Jay Dean

All Comments

Leave a comment...

  • 2 Comments
    • Jay

      Often a macro is an overkill. I use frequently other columns and a 'flag'

      to determine rows to delete, eg. In a blank column, say F1)

      =IF(OR(B1<NOW()-30,C1<NOW()-30),"x","")

      This will put an x in the column where the dates in B1 OR C1 are less then

      today - 30 days. You can the simply filter this and delete the rows.

      Someone else will probably come up with a code solution, but just another

      route

      --

      HTH

      Nick Hodge

      Southampton, England

      nick_hodge.excel.questionfor.info.btinternet.com

      "Jay Dean" <fresh1700.excel.questionfor.info.yahoo.com> wrote in message

      news:bd7dee67.0310110459.3becca74.excel.questionfor.info.posting.google.com...

      > I have an Excel sheet on which:

      > 1. columns B and D contain the same dates

      > down each column.

      > 2. From column E and up contain data

      > ** I am looking for a macro that will loop through

      > columns B and D. If it finds any row on which the

      > dates in B and D do not fall within the last month's

      > dates then

      > - it should clear those dates in B and D, and also

      > - clear all data on that row starting from Column E and

      > up.

      > - Where B and D do not contain any dates it

      > should do nothing.

      > I would appreciate your assistance very

      > much. Thanks.

      > Jay Dean

      #1; Fri, 23 May 2008 09:06:00 GMT
    • Sub ClearOldDates()

      Dim oCell As Range

      For Each oCell In Range("B:B")

      If IsDate(oCell) Then

      If Not (Year(oCell) = Year(Now()) And Month(oCell) = Month(Now())) Then

      oCell.EntireRow.Range("D1:IV1").ClearContents

      oCell.ClearContents

      End If

      End If

      Next oCell

      End Sub

      Jay Dean wrote

      > I have an Excel sheet on which:

      > 1. columns B and D contain the same dates

      > down each column.

      > 2. From column E and up contain data

      > ** I am looking for a macro that will loop through

      > columns B and D. If it finds any row on which the

      > dates in B and D do not fall within the last month's

      > dates then

      > - it should clear those dates in B and D, and also

      > - clear all data on that row starting from Column E and

      > up.

      > - Where B and D do not contain any dates it

      > should do nothing.

      > I would appreciate your assistance very

      > much. Thanks.

      > Jay Dean

      #2; Fri, 23 May 2008 09:07:00 GMT