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

 

[Zack Barresse]

Hi Jazib!

Some questions.

What column/row/range will these dates be in?
Are they actual dates, or text?
Do you want a user input? To ask you what to look for?
And you are looking to delete all the rows with dates that are not in the same month that you have signified?

Can you use a UserForm?

 

[TheBuGz]

Dear Fire Fytr Bro,

Thanx for Prompt Reply

What column/row/range will these dates be in?
it Varies in Different sheets

Are they actual dates, or text?
Actual dates

Do you want a user input? To ask you what to look for?
That will be real good if it does so .

And you are looking to delete all the rows with dates that are not in the same month that you have signified?
Yes

Can I mail u the file or should i upload it for u

 

[Zack Barresse]

You can't upload files here. On your site maybe. We should try and keep the discussion on the board as to benefit everybody. The thing I'm concerned about right now is ..

.. What column/row/range will these dates be in?
it Varies in Different sheets ..

Do you mean you want to search the entire sheet for such dates? Can you post an HTML shot of what your sheets look like? And what makes them different?

 

[macleanb]

I have made quite a few assumption in the below code - please use with care - I dont want to be responsible for zapping anything valuable:

ugly - yes - slow yes - but it'll probably work

Private Sub CommandButton1_Click()

Dim i As Long
Dim TestDate As Date

Const ColToTest = 1 ' change second number to the column you wish to test

Application.ScreenUpdating = False

TestDate = DateValue(InputBox("Enter date"))

i = 1 ' or your first row


While Cells(i, ColToTest) <> ""

retest:

If Cells(i, ColToTest) = "" Then Exit Sub


If Not (Year(Cells(i, ColToTest)) = Year(TestDate) And Month(Cells(i, ColToTest)) = Month(TestDate)) Then

Cells(i, ColToTest).EntireRow.Delete xlUp
GoTo retest
End If

i = i + 1
Wend

Application.ScreenUpdating = True

End Sub

 

[TheBuGz]

BG.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Sr.	L/C#	Description	BankGuarantee	Dateof	Dateof		Further					Amount	Commission
4				No.	Issue	Expiry		Extended						@1.6%
5	1	01/190	PPG(160)	0002/25/2001	15-Nov-2001			15-Nov-2003		15-May-2004	15-Nov-2004	15-May-2005	315,000	5,040
6	2	01/190	PPG(160)	0002/26/2001	15-Nov-2001			15-Nov-2003		15-May-2004	15-Nov-2004	15-May-2005	191,000	3,056
7	3	01/207	TDI(72)	0002/27/2001	28-Nov-2001			28-Nov-2003		28-May-2004	28-Nov-2004	28-May-2005	378,000	6,048
8	4	01/207	TDI(72)	0002/28/2001	28-Nov-2001			28-Nov-2003		28-May-2004	28-Nov-2004	28-May-2005	173,000	2,768
9	5	01/185	TDI(72)	0002/31/2001	06-Dec-2001			06-Dec-2003		06-Jun-2004	06-Dec-2004	06-Jun-2005	437,000	6,992
Bank Al-Habib Mall Rd.

 

[TheBuGz]

BG.xls
	A	B	C	D	E	F	G	H	I	J	K	L
2					2002	2003	2004	2004	2005
3	Sr.	L/C#	Description	BankGuarantee	Date	Dateof	Dateof	Dateof	Dateof	Amount	Commission	Sales
4				No.	ofIssue	Expiry	Expiry	Expiry	Expiry		@1.6%	Tax
5	1			LG/EIBL-02/36	12-Oct-2002	11-Oct-2003	11-Apr-2004	11-Oct-2004	11-Apr-2005	337,000	5,392	337,000
6	2			LG/EIBL-02/37	12-Oct-2002	11-Oct-2003	11-Apr-2004	11-Oct-2004	11-Apr-2005	204,000	3,264	-
7	3			LG/EIBL-02/38	14-Oct-2002	13-Oct-2003	13-Apr-2004	13-Oct-2004	13-Apr-2005	206,000	3,296	-
8	4			LG/EIBL-02/39	14-Oct-2002	13-Oct-2003	13-Apr-2004	13-Oct-2004	13-Apr-2005	89,000	1,424	-
Escort Investment Bank

 

[TheBuGz]

BG.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Sr.	L/C#	Description	BankGuarantee	Issue	Further	Further	Further	Further				Amount	Commission	Sales	Custom
4				No.	Date	Upto	Extended	Extended	Extended					@1.6%	Tax	Duty
5	1	99/078	PPG(240)	IIBL/AMPL/99/42	05-Jul-1999	30-Jun-2002	30-Dec-2002	29-Jun-2003	29-Dec-2003	29-Jun-2004	29-Dec-2004	29-Jun-2005	404,000	6,464	-	404,000
6	2	99/078	PPG(240)	IIBL/AMPL/99/43	05-Jul-1999	30-Jun-2002	30-Dec-2002	29-Jun-2003	29-Dec-2003	29-Jun-2004	29-Dec-2004	29-Jun-2005	404,000	6,464	-	404,000
7	3	99/078	PPG(240)	IIBL/AMPL/99/44	05-Jul-1999	30-Jun-2002	30-Dec-2002	29-Jun-2003	29-Dec-2003	29-Jun-2004	29-Dec-2004	29-Jun-2005	472,000	7,552	472,000	-
8	4	99/078	PPG(240)	IIBL/AMPL/99/45	05-Jul-1999	30-Jun-2002	30-Dec-2002	29-Jun-2003	29-Dec-2003	29-Jun-2004	29-Dec-2004	29-Jun-2005	472,000	7,552	472,000	-
Islamic Investment Bank

 

[TheBuGz]

BG.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2						2002	2003	2003	2004	2004	2005
3	Sr.	L/C#	Description	BankGuarantee	Dateof	Dateof	Dateof	Dateof	Dateof	Dateof		Amount	Commission	Sales	Custom
4				No.	Issue	Expiry	Expiry	Expiry	Expiry	Expiry			@1.6%	Tax	Duty
5	1	01/227	PPG(160)	PCBL-2001/311	26-Dec-2001	25-Dec-2002	24-Jun-2003	24-Dec-2003	24-Jun-2004	24-Dec-2004	24-Jun-2005	311,000	4,976	311,000	-
6	2	01/227	PPG(160)	PCBL-2001/312	26-Dec-2001	25-Dec-2002	24-Jun-2003	24-Dec-2003	24-Jun-2004	24-Dec-2004	24-Jun-2005	190,000	3,040		190,000
7	3	01/228	PPG(160)	PCBL-2001/313	26-Dec-2001	25-Dec-2002	24-Jun-2003	24-Dec-2003	24-Jun-2004	24-Dec-2004	24-Jun-2005	311,000	4,976	311,000	-
Platinum Commercial Bank

 

[Zack Barresse]

Okay, we've got the different sheets (is that all?). Are you wanting to search specific columns/rows? Or all cells?