Delete Rows of Data in Given Date Range

[Nathan95]

Hey All I have created a userform where users need to input Start Date (tbstartdate), End Date (tbenddate) and employer number (tbPnumber), based on this user form I want the code to be able to remove/Delete all the rows of data from the table that is located in sheet "Mark Leave" that falls between the date range and matches the employer number that was inputted in the user form. The Employer number gets inputted in Column B and Date gets inputted in Column D in the "Mark Leave" sheet.

Any help will be greatly appreciated

 

[bebo021999]

I saw you have 2 userforms, in which "Input Leave" with tbstartdate and tbenddate (are they for input leave, not remove?)
and "Remove Lease" with rtbstartdate and rtbenddate. Are these textboxes for deleting?
(In #1 you said tbstaartdate and tbenddate)

 

[Nathan95]

I saw you have 2 userforms, in which "Input Leave" with tbstartdate and tbenddate (are they for input leave, not remove?)
and "Remove Lease" with rtbstartdate and rtbenddate. Are these textboxes for deleting?
(In #1 you said tbstaartdate and tbenddate)

Hey Bebo,

Yes thats correct the input leave is for adding the leave, And the remove leave is for removing the leave between the desired dates and that match the Employee number (Pnumber) entered

 

[bebo021999]

Try this:

Private Sub remove()
Dim lr&, cell As Range
With Worksheets("Mark Leave")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    For Each cell In .Range("B2:B" & lr - 1)
        If cell.Value = rtbPnumber.Value And cell.Offset(0, 2).Value >= rtbstartdate.Value _
        And cell.Offset(0, 2).Value <= rtbstartdate.Value Then cell.Value = ""
    Next
    .Range("B2:B" & lr - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

 

[Nathan95]

Try this:

Private Sub remove()
Dim lr&, cell As Range
With Worksheets("Mark Leave")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    For Each cell In .Range("B2:B" & lr - 1)
        If cell.Value = rtbPnumber.Value And cell.Offset(0, 2).Value >= rtbstartdate.Value _
        And cell.Offset(0, 2).Value <= rtbstartdate.Value Then cell.Value = ""
    Next
    .Range("B2:B" & lr - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Thanks for the above bepo,

When i try and run it I get a "Run time error '1004': No Cells were found.

And then it highlights this line of code:

.Range("B2:B" & lr - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Nathan95]

Bump

 

[Nathan95]

Thanks for the above bepo,

When i try and run it I get a "Run time error '1004': No Cells were found.

And then it highlights this line of code:

.Range("B2:B" & lr - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Bump

 

[Nathan95]

Copy of Sick Leave Tracker V4.xlsm
	A	B	C	D	E	F
1	ID	P Number	Name	Date	Leave Type
2	W07805344743	W078053		01-07-22	AL
3	W07805344746	W078053		04-07-22	AL
4	W07805344747	W078053		05-07-22	AL
5	W07805344748	W078053		06-07-22	AL
6	W07805344749	W078053		07-07-22	AL
7	W07805344750	W078053		08-07-22	AL
8	W07805344753	W078053		11-07-22	AL
9	W07805344754	W078053		12-07-22	AL
10	W07805344755	W078053		13-07-22	AL
11	W07805344756	W078053		14-07-22	AL
12	W07805344757	W078053		15-07-22	AL
13	W07805344760	W078053		18-07-22	AL
14	W07805344761	W078053		19-07-22	AL
15	W07805344762	W078053		20-07-22	AL
16	P65446444747	P654464		05-07-22	AL
17	P65446444748	P654464		06-07-22	AL
18	P65446444749	P654464		07-07-22	AL
19	P65446444750	P654464		08-07-22	AL
20
21
22
23
24
25
26
27
28
29
30
Mark Leave

Cells with Data Validation
Cell	Allow	Criteria
D2:D1048576	Date	>1/01/2021
E2:E1048576	List	=Settings!$B$2:$B$4

 

[igold]

I read through this post and an now that you have your "Mark Leave" sheet posted, perhaps you could post the other significant data using XL2BB as well. Or maybe give us some sample variables that could be bounced against this sheet to delete the desired rows.

 

[Nathan95]

I read through this post and an now that you have your "Mark Leave" sheet posted, perhaps you could post the other significant data using XL2BB as well. Or maybe give us some sample variables that could be bounced against this sheet to delete the desired rows.

There isnt really any other significant data, as the other sheet just uses a Vlookup from this sheet. the only other thing as mentioned above is the user would need to input the P Number and the start and end date of the leave in a userform, which will then delete it from the table above.

As an example you can use P654464 with the date range 05/07/2022 - 08/07/2022

 

[igold]

Given what I read in this post and see that @bebo021999 has put some effort into this, and looking at the code he submitted in post #13, he had to pick up those variable names from something that I did not see. All that being said, I tried to make this so it can be easily converted to pick up the required data from some other source and substituted in. I used an InputBox to enter the variables in the interim and essentially used his code with some minor changes. This code tested on your supplied data, which I believe to be a Range of data as opposed to a Table.

Sub remove()

    Dim lRow As Long, cel As Range, i As Long, rw As String, spl
    
    rw = Application.InputBox("Please enter P Number, Start Date, End Date separated by comma's only", _
        "Data Entry", "P Number,Start Date,End Date", , , , , 2)
    spl = Split(rw, ",")
    If UBound(spl) <> 2 Or rw = "" Then
        MsgBox "Please make sure all three pieces of data are separated by a comma", vbCritical
        Exit Sub
    End If
    With Worksheets("Mark Leave")
        lRow = .Cells(Rows.Count, "B").End(xlUp).Row
        For Each cel In .Range("B2:B" & lRow)
            If cel.Value = Trim(spl(0)) And CDate(cel.Offset(0, 2)) >= Trim(spl(1)) _
            And CDate(cel.Offset(0, 2)) <= Trim(spl(2)) Then cel.Value = ""
        Next
        .Range("B2:B" & lRow).SpecialCells(4).EntireRow.Delete
    End With
    
End Sub