Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows whose value is not between 2 dates
I have a report that is imported into excel. I dont need all the data.
Column E has dates. I'd like to delete the rows that arent between 2 dates. for example, when the macro is run, it'll ask me for a beginning date and a ending date and delete all the rows that dont equal. any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows whose value is not between 2 dates
"Dominique Feteau" wrote:
I have a report that is imported into excel. I dont need all the data. Column E has dates. I'd like to delete the rows that arent between 2 dates. You can use the value property of Range's Cells property to grab the data out of the cell in column E. Delete the row if it doesn't meet your criteria. Here's some code to get you started. This assumes you've selected all the relevant data before you start the macro: numRows = Selection.Rows.Count startDateString = InputBox("Enter a start date:", "Start Date", "MM/DD/YY") If (IsDate(startDateString)) Then startDate = CDate(startDateString) For i = numRows To 1 Step -1 If Selection.Rows(i).Cells(1, 5).Value < startDate Then Selection.Rows(i).Delete End If Next i End If IsDate checks that you entered a string in the InputBox that can be converted to a date. CDate actually makes a Date type out of the string. This assumes you have real dates in column E. If your local settings are not US, you may have to make some adjustment. I'm not sure CDate is global-friendly. But you have to have your types right if you want the comparisons to work as expected. Cells(1,5) is the cell in row 1 (the current row) of the Selection.Rows(i) row, in column E. If other people are going to use your macro, you have a lot of extra work to make this macro more bulletproof. And if you don't want to do any work to set things up before the macro runs, that's some extra code. --Shawn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows whose value is not between 2 dates
One way:
Enter the Start Date in F1 and Finish Date in G1 Then run this macro Sub DeleteDates() Dim StDate As Date, FinDate As Date Dim LastRow As Integer StDate = Range("F1").Value FinDate = Range("G1").Value LastRow = Cells(Rows.Count, 5).End(xlUp).Row For I = 2 To LastRow If Cells(I, 5).Value = StDate And Cells(I, 5).Value <= FinDate Then Cells(I, 8).Value = "D" End If Next Range("H1").Select 'Note Field:=8 assumes columns A-D have data, otherwise Field could = 4 Selection.AutoFilter Field:=8, Criteria1:="D" Range("A2:H" & LastRow).Select Selection.Delete Selection.AutoFilter Range("A1").Select End Sub Glen "Dominique Feteau" wrote in message ... I have a report that is imported into excel. I dont need all the data. Column E has dates. I'd like to delete the rows that arent between 2 dates. for example, when the macro is run, it'll ask me for a beginning date and a ending date and delete all the rows that dont equal. any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows whose value is not between 2 dates
I'm sure Occam would have coded
Sub DeleteDates() Dim StDate As Date, FinDate As Date, LastRow&, i& StDate = InputBox("Beginning Date?") FinDate = InputBox("Ending Date?") LastRow = Cells(Rows.Count, 5).End(xlUp).Row For i = LastRow To 2 Step -1 If Cells(i, 5).Value = StDate And _ Cells(i, 5).Value <= FinDate Then Rows(i).delete End If Next i End Sub Glen wrote: One way: Enter the Start Date in F1 and Finish Date in G1 Then run this macro Sub DeleteDates() Dim StDate As Date, FinDate As Date Dim LastRow As Integer StDate = Range("F1").Value FinDate = Range("G1").Value LastRow = Cells(Rows.Count, 5).End(xlUp).Row For I = 2 To LastRow If Cells(I, 5).Value = StDate And Cells(I, 5).Value <= FinDate Then Cells(I, 8).Value = "D" End If Next Range("H1").Select 'Note Field:=8 assumes columns A-D have data, otherwise Field could = 4 Selection.AutoFilter Field:=8, Criteria1:="D" Range("A2:H" & LastRow).Select Selection.Delete Selection.AutoFilter Range("A1").Select End Sub Dominique wrote: I have a report that is imported into excel. I dont need all the data. Column E has dates. I'd like to delete the rows that arent between 2 dates. for example, when the macro is run, it'll ask me for a beginning date and a ending date and delete all the rows that dont equal. any suggestions? ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows whose value is not between 2 dates
I like your way better. Much more elegant.
Glen "Dave D-C" wrote in message ... I'm sure Occam would have coded Sub DeleteDates() Dim StDate As Date, FinDate As Date, LastRow&, i& StDate = InputBox("Beginning Date?") FinDate = InputBox("Ending Date?") LastRow = Cells(Rows.Count, 5).End(xlUp).Row For i = LastRow To 2 Step -1 If Cells(i, 5).Value = StDate And _ Cells(i, 5).Value <= FinDate Then Rows(i).delete End If Next i End Sub Glen wrote: One way: Enter the Start Date in F1 and Finish Date in G1 Then run this macro Sub DeleteDates() Dim StDate As Date, FinDate As Date Dim LastRow As Integer StDate = Range("F1").Value FinDate = Range("G1").Value LastRow = Cells(Rows.Count, 5).End(xlUp).Row For I = 2 To LastRow If Cells(I, 5).Value = StDate And Cells(I, 5).Value <= FinDate Then Cells(I, 8).Value = "D" End If Next Range("H1").Select 'Note Field:=8 assumes columns A-D have data, otherwise Field could = 4 Selection.AutoFilter Field:=8, Criteria1:="D" Range("A2:H" & LastRow).Select Selection.Delete Selection.AutoFilter Range("A1").Select End Sub Dominique wrote: I have a report that is imported into excel. I dont need all the data. Column E has dates. I'd like to delete the rows that arent between 2 dates. for example, when the macro is run, it'll ask me for a beginning date and a ending date and delete all the rows that dont equal. any suggestions? ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete rows with particular dates present | Excel Programming | |||
Delete Rows with particular dates | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |