Excel VBA - Delete rows within a date range
 

Hi I'm new to vba programming, here is my problem. I have 2 variable
one named start_date and the other end_date. What I am trying to do i
go through a worksheet and look at a cell in each row called startdat
and compare it to the dates that were entered into the variables above
If the date in the cell in each row is not = to the start_dat
variable and is not <= the end_date variable then I want to delete tha
entire row. Basically if the date in the cell is not within the dat
range of the two variables, then that row needs to be deleted.

It needs to go through the whole worksheet to eliminate all rows wit
dates that don't fall within this range.

Can someone please help I am desperate.
Thank

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete rows within a date range
 

rott,

If your dates are in column A, then you can use the macro below. If the
dates are in another column, change all instances of [1] to correspond to
the actual column # - if the dates are in column H, for example, use [8].
You can either change the start and end dates in my code, or use global
variables to pass the dates in (in which case delete the Dim
statements......)

HTH,
Bernie
MS Excel MVP

Sub KeepDates()
Dim myRows As Long
Dim Start_Date As Date
Dim End_Date As Date
Start_Date = "1/2/4"
End_Date = "1/7/4"

Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(ISNUMBER(RC[1]),IF(AND(RC[1]=DATEVALUE(""1/2/4"")," & _
"RC[1]<=DATEVALUE(""1/7/4"")), ""Keep"",""Trash""),""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete

End Sub

"rott " wrote in message
...
Hi I'm new to vba programming, here is my problem. I have 2 variables
one named start_date and the other end_date. What I am trying to do is
go through a worksheet and look at a cell in each row called startdate
and compare it to the dates that were entered into the variables above.
If the date in the cell in each row is not = to the start_date
variable and is not <= the end_date variable then I want to delete that
entire row. Basically if the date in the cell is not within the date
range of the two variables, then that row needs to be deleted.

It needs to go through the whole worksheet to eliminate all rows with
dates that don't fall within this range.

Can someone please help I am desperate.
Thanks


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete rows within a date range
 

Hey thanks man I will try that out, but i got one question the dates ar
all in column G So by your macro that would be number 7 that i change.
My question is do i change all of the A's used in the code to G's fo
column G or do i just change all the numbers.


thanks again

rot

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete rows within a date range
 

Rott,

Just the numbers: a new column A is inserted and used for a "flagging"
formula, as is then deleted after use.

Bernie
MS Excel MVP

"rott " wrote in message
...
Hey thanks man I will try that out, but i got one question the dates are
all in column G So by your macro that would be number 7 that i change.
My question is do i change all of the A's used in the code to G's for
column G or do i just change all the numbers.


thanks again

rott


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete rows within a date range
 

Hey Bernie sorry to bug you again but when i put this code into my macro
it always stops at this line:

Columns("A:A").Find(What:="Trash", After:=Range("A7")).Select

and gives this error message:

Runtime error 91 - Object Variable or withblock variable not set.

The only thing I can think of is the portion that says "What" right
after Find. Is this supposed to be defined somewhere?

I really appreciate the help. If I can get this working it would be
fantastic.

thanks
rott


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete rows within a date range
 

rott,

Sorry for not being clear, but when I said change all instances of [1] to
[7] I meant only those instances of 1s inside square brackets. Copy the
code below and give it a try. I have made the changes as needed, so don't
change anything.

HTH,
Bernie
MS Excel MVP

Sub KeepDates()
Dim myRows As Long
Dim Start_Date As Date
Dim End_Date As Date
Start_Date = "1/2/4"
End_Date = "1/7/4"

Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(ISNUMBER(RC[7]),IF(AND(RC[7]=DATEVALUE(""1/2/4"")," & _
"RC[7]<=DATEVALUE(""1/7/4"")), ""Keep"",""Trash""),""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete

End Sub


"rott " wrote in message
...
Hey Bernie sorry to bug you again but when i put this code into my macro
it always stops at this line:

Columns("A:A").Find(What:="Trash", After:=Range("A7")).Select

and gives this error message:

Runtime error 91 - Object Variable or withblock variable not set.

The only thing I can think of is the portion that says "What" right
after Find. Is this supposed to be defined somewhere?

I really appreciate the help. If I can get this working it would be
fantastic.

thanks
rott


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete rows within a date range
 

Hey thanks man it works now. I just have to change it to be able to ge
the date from the variables instead of having the date hard coded in.

The start_date and End_date variables are pulling the dates from a cel
on another worksheet. So it has to work with those dates

Would this work if I replaced this:

DATEVALUE(""1/7/4"")

with the variable for the start date instead like this

DATEVALUE(Start_date)

and then do the same for the end_date variable. Or is there somethin
else i need to do to make that change

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete rows within a date range
 

rott,

Try this, below.

HTH,
Bernie
MS Excel MVP

Sub KeepDates()
Dim myRows As Long
Dim Start_Date As Date
Dim End_Date As Date
'Change the sheet and range address as appropriate
Start_Date = Worksheets("Sheet1").Range("D4").Value
End_Date = Worksheets("Sheet1").Range("D5").Value

Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(ISNUMBER(RC[7]),IF(AND(RC[7]=DATEVALUE(""" _
& Start_Date & """)," & _
"RC[7]<=DATEVALUE(""" & End_Date & _
""")), ""Keep"",""Trash""),""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete

End Sub

"rott " wrote in message
...
Hey thanks man it works now. I just have to change it to be able to get
the date from the variables instead of having the date hard coded in.

The start_date and End_date variables are pulling the dates from a cell
on another worksheet. So it has to work with those dates

Would this work if I replaced this:

DATEVALUE(""1/7/4"")

with the variable for the start date instead like this

DATEVALUE(Start_date)

and then do the same for the end_date variable. Or is there something
else i need to do to make that change?


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete rows within a date range
 

Well i tried that and it work great thanks again.

I have another question. I want to adapt the code to delete rows tha
have the text "No Impact" in column 12 (Column L). This is what
have so far but i get the same error as before.

Can you take a quick peek and tell me where i am going wrong. Thank
again man this is such a great help.

--------------------------------------------
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(RC[12])=""No Impact""" _
& """), ""Keep"",""Trash""),""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
-------------------------------------------------

--
Message posted from http://www.ExcelForum.com