Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 18th 09, 12:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 10
Default Macro to delete rows based on date

I have a spreadsheet with completion dates in column C. I want my macro to
look at each date, check to see if it's older than 30 days, and then delete
the rows that are older than 30 days. I have a macro that seems to work, but
I can't get it to loop (and I tried many times to make that happen). I'm
missing the loop piece, but also want to make sure I've put the "formula"
part in properly. Here's my macro:

Public Sub Delete1()
Range("c3").Select
If ActiveCell.Value <= "TODAY()-30" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

Thanks,
PMO

  #2   Report Post  
Old February 18th 09, 12:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default Macro to delete rows based on date

Maybe

Public Sub Delete1()
lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = lastrow To 3 Step -1
If Cells(x, 3).Value <= Date - 30 Then
Cells(x, 3).EntireRow.Delete
End If
Next
End Sub

Mike

"PMBO" wrote:

I have a spreadsheet with completion dates in column C. I want my macro to
look at each date, check to see if it's older than 30 days, and then delete
the rows that are older than 30 days. I have a macro that seems to work, but
I can't get it to loop (and I tried many times to make that happen). I'm
missing the loop piece, but also want to make sure I've put the "formula"
part in properly. Here's my macro:

Public Sub Delete1()
Range("c3").Select
If ActiveCell.Value <= "TODAY()-30" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

Thanks,
PMO

  #3   Report Post  
Old February 18th 09, 01:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 10
Default Macro to delete rows based on date

PERFECT! No wonder you're gold level. Thank You.

"PMBO" wrote:

I have a spreadsheet with completion dates in column C. I want my macro to
look at each date, check to see if it's older than 30 days, and then delete
the rows that are older than 30 days. I have a macro that seems to work, but
I can't get it to loop (and I tried many times to make that happen). I'm
missing the loop piece, but also want to make sure I've put the "formula"
part in properly. Here's my macro:

Public Sub Delete1()
Range("c3").Select
If ActiveCell.Value <= "TODAY()-30" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

Thanks,
PMO

  #4   Report Post  
Old February 18th 09, 01:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default Macro to delete rows based on date

Your welcome, glad I could help

"PMBO" wrote:

PERFECT! No wonder you're gold level. Thank You.

"PMBO" wrote:

I have a spreadsheet with completion dates in column C. I want my macro to
look at each date, check to see if it's older than 30 days, and then delete
the rows that are older than 30 days. I have a macro that seems to work, but
I can't get it to loop (and I tried many times to make that happen). I'm
missing the loop piece, but also want to make sure I've put the "formula"
part in properly. Here's my macro:

Public Sub Delete1()
Range("c3").Select
If ActiveCell.Value <= "TODAY()-30" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

Thanks,
PMO

  #5   Report Post  
Old February 18th 09, 01:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default Macro to delete rows based on date

You may like this to do all without a loop. I have used line continuation _
to prevent word wrap

Sub filterdates()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("c1:c" & lr).AutoFilter Field:=1, _
Criteria1:="<" & Date - 30
Range("c2:c" & lr) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("c1:c" & lr).AutoFilter
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PMBO" wrote in message
...
I have a spreadsheet with completion dates in column C. I want my macro to
look at each date, check to see if it's older than 30 days, and then
delete
the rows that are older than 30 days. I have a macro that seems to work,
but
I can't get it to loop (and I tried many times to make that happen). I'm
missing the loop piece, but also want to make sure I've put the "formula"
part in properly. Here's my macro:

Public Sub Delete1()
Range("c3").Select
If ActiveCell.Value <= "TODAY()-30" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

Thanks,
PMO




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Rows based on value Sabosis Excel Worksheet Functions 4 October 28th 08 11:21 PM
Delete Rows Based on Words Cue Excel Discussion (Misc queries) 2 June 4th 08 06:42 AM
Macro to delete rows based on a condition Darrilyn Excel Worksheet Functions 1 September 6th 07 12:12 AM
Delete rows based on criteria Chris_t_2k5 Excel Discussion (Misc queries) 2 April 11th 06 01:52 PM
Delete rows based on certain criteria Coal Miner Excel Discussion (Misc queries) 2 March 3rd 06 05:56 PM


All times are GMT +1. The time now is 08:09 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017