Deleting Entire Rows above and below a specific range

[harnden2005]

Hi, I'm having a real problem trying to delete entire rows above and below a certain range using VBA macro.

I have a userform made up which allows the user to select the From (mm/yyyy) and To (mm/yyyy) dates. So that the user can pull out a wanted range from the raw data. the data is contained within A1:C300 range. Where the first coloumn is Date i.e. 07/1999.

So what i was thinking is to write a loop that compares the dates to the dates entered by the user in the userform and if the date less than From Date delete all the rows above that date and if the date is greater than the To date entered by the user it should delete all the rows below that date. I wrote a code but it only works for the From date and when I use the same code for the To date it deletes everything.

here is my code that I got from your website:

Sub DateFormat(Data)
'Compares date with the date that the user input and gives out a range of exchange rates_
'beginning with that date


Dim LR As Date
LR = Range("a" & Rows.count).End(xlUp).row

For r = LR To 4 Step -1
If Cells(r, "a").Value < Data Then Rows(r).Delete
Next r

End Sub

Sub DateFormat1(Data)

Dim LR As Date
LR = Range("a" & Rows.count).End(xlUp).row

For r = LR To 4 Step -1
If Cells(r, "a").Value > Data1 Then Rows(r).Delete
Next r
End Sub

where

Dim Data As Date

Data = .ComboBox1 & "/" & .ComboBox2

Dim Data1 As Date

Data1 = .ComboBox3 & "/" & .ComboBox4

code is in userform command button


Any help would be much appreciated,

Thank you

 

[Legacy 68668]

Dim LR As Date
LR = Range("a" & Rows.count).End(xlUp).row

Data type are not matched.
LR should be Long

You also have comboboxes... and you will need to convert that sting value to a date value....

I think you can use autofilter...

 

[GTO]

Greetings Harnden,

A slow typist on this end, but I had already started, so will tack this onto what Seiya advised. Far as I know, regardless of the formatting for the cells in col A, if you loook in the formula bar, they'll include the day and I think including the day will be easier in coercing dates from the combo boxes.

By example, for your four comboboxes and a commandbutton, see if this makes sense:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><SPAN style="color:#00007F">Dim</SPAN> dtmStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, dtmEnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    Me.Hide<br>    <br>    dtmStart = DateSerial(ComboBox2.Value, ComboBox1.Value, 1)<br>    dtmEnd = DateSerial(ComboBox4.Value, ComboBox3.Value, 1)<br>    <br>    LR = Cells(Rows.Count, 1).End(xlUp).Row<br>    <br>    <SPAN style="color:#00007F">For</SPAN> r = LR <SPAN style="color:#00007F">To</SPAN> 4 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Cells(r, 1).Value < dtmStart _<br>        <SPAN style="color:#00007F">Or</SPAN> Cells(r, 1).Value > dtmEnd <SPAN style="color:#00007F">Then</SPAN><br>            Cells(r, 1).EntireRow.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>    <SPAN style="color:#007F00">'// Starting Month</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ComboBox1<br>        .List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'//Starting year</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ComboBox2<br>        .List = Array(1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Ending Month</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ComboBox3<br>        .List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Ending Year</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ComboBox4<br>        .List = Array(1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps,

Mark

 

[harnden2005]

Thank you so much GTO the code works perfectly!!! And thank you Seiya for your input too.

Best regards,

Alex

 

[GTO]

Happy to help