Deleting Rows of UnWanted DATA

[TheBuGz]

Dear Forum members,

I have a work book with around ten sheets in it.

It conatins data in date format mm-ddd-yyyy.

i want that if i write Jan-2005 all rows are deleted excep those containing the value from 01-Jan-2005 to 31-Jan-2005 and like that.


That date ranges is from 05-Jul-1997 to 02-Feb-2005 and continues to grow and the dates are not in spread in several columns and not in one.

Anyone who could come forward and help me out.

Regards

 

[TheBuGz]

all cells

 

[Zack Barresse]

Okay, this makes it a little more difficult. You need to specify some more things. If a date is found not in the month/year you specify, you say you want it deleted. There are a few options:

1) Delete and Shift cells below it up.

2) Delete and Shift cells right to left.

3) Check entire row, if any, delete entire row.

4) Check entire column, if any, delete entire column.

Or any others?

 

[TheBuGz]

Check entire row, if not found delete entire row.

 

[TheBuGz]

bump

 

[Zack Barresse]

What I would do ...


Create a UserForm.
Create two ComboBox's
ComboBox1 as the Month
ComboBox2 as the Year
Create two CommandButton's
CommandButton1 as the 'Ok' button.
CommandButton2 as the 'Cancel' Button.
In Excel create a CommandButton from the Forms menu. *
In your UserForm code, use something like this ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
<SPAN style="color:#007F00">' OK BUTTON</SPAN>
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, sDate, eDate
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, inRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    sDate = DateSerial(CInt(Me.ComboBox2.Value), _
        <SPAN style="color:#00007F">CInt</SPAN>(Month(Me.ComboBox1.List(Me.ComboBox1.List(, 2), 2))), 1)
    eDate = DateSerial(CInt(Me.ComboBox2.Value), _
        <SPAN style="color:#00007F">CInt</SPAN>(Month(Me.ComboBox1.List(Me.ComboBox1.List(, 2), 2))) + 1, 1) - 1
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
        lRow = ws.UsedRange.Rows.Count
        lCol = ws.UsedRange.Columns.Count
        <SPAN style="color:#00007F">For</SPAN> r = lRow <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1 <SPAN style="color:#007F00">'headers</SPAN>
            inRow = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> c = lCol <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
                <SPAN style="color:#00007F">If</SPAN> IsDate(ws.Cells(r, c)) <SPAN style="color:#00007F">Then</SPAN>
                    <SPAN style="color:#00007F">If</SPAN> ws.Cells(r, c) <= eDate <SPAN style="color:#00007F">Then</SPAN>
                        <SPAN style="color:#00007F">If</SPAN> ws.Cells(r, c) >= sDate <SPAN style="color:#00007F">Then</SPAN>
                            inRow = <SPAN style="color:#00007F">True</SPAN>
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> c
            <SPAN style="color:#00007F">If</SPAN> inRow = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> ws.Rows(r).EntireRow.Delete
        <SPAN style="color:#00007F">Next</SPAN> r
    <SPAN style="color:#00007F">Next</SPAN> ws
    Unload Me
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
<SPAN style="color:#007F00">' CANCEL BUTTON</SPAN>
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
<SPAN style="color:#007F00">' SET MONTHS/YEARS</SPAN>
<SPAN style="color:#007F00">'/////////////////////////////////</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> m <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">For</SPAN> m = 1 <SPAN style="color:#00007F">To</SPAN> 12 <SPAN style="color:#00007F">Step</SPAN> 1
        Me.ComboBox1.AddItem MonthName(m)
        Me.ComboBox1.List(m - 1, 2) = m
    <SPAN style="color:#00007F">Next</SPAN> m
    <SPAN style="color:#00007F">For</SPAN> y = Year(Date) <SPAN style="color:#00007F">To</SPAN> Year(Date) + 10 <SPAN style="color:#00007F">Step</SPAN> 1
        Me.ComboBox2.AddItem y
    <SPAN style="color:#00007F">Next</SPAN> y
    Me.ComboBox1.ListIndex = 0
    Me.ComboBox2.ListIndex = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


* Form for excel button to call userform is something like ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ShowMyForm()
    Load UserForm1
    UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Note, the above UserForm code will check the entire row. If a date in the range you specify (of the entire month) is found, that row will NOT be deleted. If no date in that row is found within the month/year you specify, the entire row WILL be deleted.


HTH