Delete Macro

[maninjapan]

I have 2 lists of stock prices side by side in the same time increments but the start and finish times are different. I would like to delete the times that are outside the other one so the data from the 2 match.
Should jsut be a simple loop right?
This is what I have so far, I understand it might not make much sense as it stands. However any help I can get on this would be much appreciated.

Thanks

Sub DataDelete()

Do Until Selection.Value = "EndofData"
If Selection.Value > 1401 And Selection.Value < 944 Then
Range("A:E").Select
Selection.Delete Shift:=xlUp
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("B1").Select
End Sub

[Bob Phillips]

When deleting, always work from the bottom up.

Something like



EndRow = Columns(1).Find("EndofData"
For i = EndRow - 1 To 1 Step -1

If Cells(i, "A").Value 1401 And Cells(i, "A").Value < 944 Then

Cells(i, "A").Resize(,5).Delete Cells(i, "A").
End If
Next i

[maninjapan]

Thanks Xld looks like this now but there seems to be an error in the first line 'EndRow......."


Sub DataDelete()

EndRow = Columns(1).Find("EndofData")
For i = EndRow - 1 To 1 Step -1

If Cells(i, "B").Value > 1401 And Cells(i, "B").Value < 944 Then

Cells(i, "B").Resize(, 5).Delete Cells(i, "B")
End If
Next i
End Sub

[GTO]

Hi there,

Try:


EndRow = Columns(1).Find("EndofData").Row


Reference the test:


If Cells(i, "B").Value > 1401 And Cells(i, "B").Value < 944 Then


Do you mean IF the cell is greater than 1401 OR less than 944? Either that or maybe less than 1401 AND greater than 944.

Greater than 1401 AND less than 944 ain't gonna happen :-)

Hope that helps,

Mark

[maninjapan]

right, I see your point. Its actually a time though, I want to delete between 1400hrs and 945hrs

[Bob Phillips]

Is that 14:00 and 21:45, or are you checking for 14:00 and 9:45 the next day?

[maninjapan]

xld, sorry didnt see this reply, yes I was reffering to 14:00 today and 9:45 am tomorrow.
Thanks

[maninjapan]

I have come up with a simple solution for now, just splitting it into 2 groups (>1400 and < 2400) or (>100 and < 945)