Deleting Rows of UnWanted DATA

TheBuGz

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
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 ..
Click to expand...

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?
 
Upvote 0
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
 
Upvote 0
BG.xls
ABCDEFGHIJKLMN
3Sr.L/C#DescriptionBankGuaranteeDateofDateofFurtherAmountCommission
4No.IssueExpiryExtended@1.6%
5101/190PPG(160)0002/25/200115-Nov-200115-Nov-200315-May-200415-Nov-200415-May-2005315,0005,040
6201/190PPG(160)0002/26/200115-Nov-200115-Nov-200315-May-200415-Nov-200415-May-2005191,0003,056
7301/207TDI(72)0002/27/200128-Nov-200128-Nov-200328-May-200428-Nov-200428-May-2005378,0006,048
8401/207TDI(72)0002/28/200128-Nov-200128-Nov-200328-May-200428-Nov-200428-May-2005173,0002,768
9501/185TDI(72)0002/31/200106-Dec-200106-Dec-200306-Jun-200406-Dec-200406-Jun-2005437,0006,992
Bank Al-Habib Mall Rd.
 
Upvote 0
BG.xls
ABCDEFGHIJKL
220022003200420042005
3Sr.L/C#DescriptionBankGuaranteeDateDateofDateofDateofDateofAmountCommissionSales
4No.ofIssueExpiryExpiryExpiryExpiry@1.6%Tax
51LG/EIBL-02/3612-Oct-200211-Oct-200311-Apr-200411-Oct-200411-Apr-2005337,0005,392337,000
62LG/EIBL-02/3712-Oct-200211-Oct-200311-Apr-200411-Oct-200411-Apr-2005204,0003,264-
73LG/EIBL-02/3814-Oct-200213-Oct-200313-Apr-200413-Oct-200413-Apr-2005206,0003,296-
84LG/EIBL-02/3914-Oct-200213-Oct-200313-Apr-200413-Oct-200413-Apr-200589,0001,424-
Escort Investment Bank
 
Upvote 0
BG.xls
ABCDEFGHIJKLMNOP
3Sr.L/C#DescriptionBankGuaranteeIssueFurtherFurtherFurtherFurtherAmountCommissionSalesCustom
4No.DateUptoExtendedExtendedExtended@1.6%TaxDuty
5199/078PPG(240)IIBL/AMPL/99/4205-Jul-199930-Jun-200230-Dec-200229-Jun-200329-Dec-200329-Jun-200429-Dec-200429-Jun-2005404,0006,464-404,000
6299/078PPG(240)IIBL/AMPL/99/4305-Jul-199930-Jun-200230-Dec-200229-Jun-200329-Dec-200329-Jun-200429-Dec-200429-Jun-2005404,0006,464-404,000
7399/078PPG(240)IIBL/AMPL/99/4405-Jul-199930-Jun-200230-Dec-200229-Jun-200329-Dec-200329-Jun-200429-Dec-200429-Jun-2005472,0007,552472,000-
8499/078PPG(240)IIBL/AMPL/99/4505-Jul-199930-Jun-200230-Dec-200229-Jun-200329-Dec-200329-Jun-200429-Dec-200429-Jun-2005472,0007,552472,000-
Islamic Investment Bank
 
Upvote 0
BG.xls
ABCDEFGHIJKLMNO
2200220032003200420042005
3Sr.L/C#DescriptionBankGuaranteeDateofDateofDateofDateofDateofDateofAmountCommissionSalesCustom
4No.IssueExpiryExpiryExpiryExpiryExpiry@1.6%TaxDuty
5101/227PPG(160)PCBL-2001/31126-Dec-200125-Dec-200224-Jun-200324-Dec-200324-Jun-200424-Dec-200424-Jun-2005311,0004,976311,000-
6201/227PPG(160)PCBL-2001/31226-Dec-200125-Dec-200224-Jun-200324-Dec-200324-Jun-200424-Dec-200424-Jun-2005190,0003,040190,000
7301/228PPG(160)PCBL-2001/31326-Dec-200125-Dec-200224-Jun-200324-Dec-200324-Jun-200424-Dec-200424-Jun-2005311,0004,976311,000-
Platinum Commercial Bank
 
Upvote 0
Okay, we've got the different sheets (is that all?). Are you wanting to search specific columns/rows? Or all cells?
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
561
skyh3ck
skyh3ck
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D
R
  • Question Question
Automate data pull from one file to another
Replies
1
Views
467
John_w
J
M
  • Question Question
Deleting unuse row before transfering into pdf
Replies
1
Views
285
NoSparks
N
C
  • Question Question
VBA Data Munging - deleting unwanted stuff
Replies
2
Views
511
Micron
M

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top