Excel VBA - Delete rows within a date range

  • Thread starter Thread starter rott
  • Start date Start date
R

rott

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
rott,

Change

Range("A1").FormulaR1C1 = _
"=IF(RC[12])=""No Impact""" _
& """), ""Keep"",""Trash""),""Keep"")"

to

Range("A1").FormulaR1C1 = _
"=IF(RC[12]=""No Impact"",""Trash"",""Keep"")"

HTH,
Bernie
MS Excel MVP
 
Back
Top