Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Hi, all,
I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Sarah,
Try code like the following. Change the reference to A1:A100 to the appropriate range. Sub AAA() Dim Keep1 As Date Dim Keep2 As Date Dim R As Range Dim RR As Range Keep1 = CDate(Application.WorksheetFunction.Large(Range("A 1:A100"), 10)) Keep2 = Application.WorksheetFunction.Max(Range("A1:A100") ) Set R = Cells(Rows.Count, "A").End(xlUp) Do Until R.Row = 1 If Keep1 <= R.Value And R.Value <= Keep2 Then ' R is within 10 entries. do nothing. Else If RR Is Nothing Then Set RR = R Else Set RR = Application.Union(RR, R) End If End If Set R = R(0, 1) Loop If Not RR Is Nothing Then RR.EntireRow.Delete End If End Sub Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com The San Diego Project Group, LLC (email is on the web site) USA Central Daylight Time (-5:00 GMT) On Sat, 11 Oct 2008 18:28:49 +0200, "Sarah H." wrote: Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
I think this code will do what you want (change the worksheet reference in
the With statement and the DateStartRow and DateColumn Const statements to suit your needs)... Sub CheckForSixItems() Dim X As Long Dim R As Range Dim LastRow As Long Dim LargeDateCells As Range Const DateStartRow As Long = 2 Const DateColumn As String = "B" With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row For X = 1 To 10 If LargeDateCells Is Nothing Then Set LargeDateCells = .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X))) Else Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) End If Next For Each R In Range(.Cells(DateStartRow, DateColumn), _ .Cells(LastRow, DateColumn)) If Intersect(R, LargeDateCells) Is Nothing Then R.Clear Next End With End Sub -- Rick (MVP - Excel) "Sarah H." wrote in message ... Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Sorry about the subroutine name (I reused a macro from another question I
answered early)... the code should work fine as posted... just change the subroutine name to anything you think is more proper if you want. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I think this code will do what you want (change the worksheet reference in the With statement and the DateStartRow and DateColumn Const statements to suit your needs)... Sub CheckForSixItems() Dim X As Long Dim R As Range Dim LastRow As Long Dim LargeDateCells As Range Const DateStartRow As Long = 2 Const DateColumn As String = "B" With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row For X = 1 To 10 If LargeDateCells Is Nothing Then Set LargeDateCells = .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X))) Else Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) End If Next For Each R In Range(.Cells(DateStartRow, DateColumn), _ .Cells(LastRow, DateColumn)) If Intersect(R, LargeDateCells) Is Nothing Then R.Clear Next End With End Sub -- Rick (MVP - Excel) "Sarah H." wrote in message ... Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Most interesting approach, Chip. Thank you.
I studied it until I could see what you did. Very useful for me! For example, I found it interesting how you saved up the union of rows to delete and then did the actual deletion at the end. One thing is, I don't want to delete the whole row for dates older than the most recent 10 -- but only the older dates in the date column. I figured out how to change your code to do that, though. I also found Rick's code to be extremely useful. Both of these are excellent, and I am grateful! I can now do what I need to do. I will respond to Rick separately. Sarah ------------------ "Chip Pearson" wrote in message ... Sarah, Try code like the following. Change the reference to A1:A100 to the appropriate range. Sub AAA() Dim Keep1 As Date Dim Keep2 As Date Dim R As Range Dim RR As Range Keep1 = CDate(Application.WorksheetFunction.Large(Range("A 1:A100"), 10)) Keep2 = Application.WorksheetFunction.Max(Range("A1:A100") ) Set R = Cells(Rows.Count, "A").End(xlUp) Do Until R.Row = 1 If Keep1 <= R.Value And R.Value <= Keep2 Then ' R is within 10 entries. do nothing. Else If RR Is Nothing Then Set RR = R Else Set RR = Application.Union(RR, R) End If End If Set R = R(0, 1) Loop If Not RR Is Nothing Then RR.EntireRow.Delete End If End Sub Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com The San Diego Project Group, LLC (email is on the web site) USA Central Daylight Time (-5:00 GMT) On Sat, 11 Oct 2008 18:28:49 +0200, "Sarah H." wrote: Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Rick,
Your code helped me immensely. Thank you. (The name of the macro did not confuse me beyond a few seconds, but thank you for the follow-up clarification.) I think I mostly understand what you did. I have to admit that part of it didn't seem to work. I get a run-time error 1004, Unable to get the Large property, at this part (from the Else statement): Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) Although I don't see exactly why the error occurs, I will add that I have now used a combination of what you and Chip wrote to get an answer that works fine for me. Still, I am curious as to what went wrong here -- if you don't mind looking further. (And what is CDate? Both you and Chip used it. Well, now I've found what it is via a web search, but I still don't quite see why the value in the date column isn't sufficient on its own so that we need to convert it to CDate. Maybe you can explain that part?) After I digested yours and Chip's I started thinking of a new approach. I don't know if it's better or worse, but it makes sense to me, so I'll ask what you guys think: what about just finding the 10th most recent date (via the same "WorksheetFunction.Large(...)" technique you both suggested) and then simply deleting cells that are smaller than that value? Well, that's what I've now coded. I still have to see about error-checking in case there aren't 10 dates, though. But wait -- if there aren't, I don't need to delete anything anyway, so I can just exit on such an error, I guess. Thanks so much, again. Sarah "Rick Rothstein" wrote in message ... I think this code will do what you want (change the worksheet reference in the With statement and the DateStartRow and DateColumn Const statements to suit your needs)... Sub CheckForSixItems() Dim X As Long Dim R As Range Dim LastRow As Long Dim LargeDateCells As Range Const DateStartRow As Long = 2 Const DateColumn As String = "B" With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row For X = 1 To 10 If LargeDateCells Is Nothing Then Set LargeDateCells = .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X))) Else Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) End If Next For Each R In Range(.Cells(DateStartRow, DateColumn), _ .Cells(LastRow, DateColumn)) If Intersect(R, LargeDateCells) Is Nothing Then R.Clear Next End With End Sub -- Rick (MVP - Excel) "Sarah H." wrote in message ... Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Whoops, Rick -- my bad. Your code does work.
The problem was that I tested it when I had fewer than 10 dates in the date column. I added an "On Error" statement and now it works. Still wondering about my other questions, though. Thanks again! Sarah ----------------- "Sarah H." wrote in message ... Rick, Your code helped me immensely. Thank you. (The name of the macro did not confuse me beyond a few seconds, but thank you for the follow-up clarification.) I think I mostly understand what you did. I have to admit that part of it didn't seem to work. I get a run-time error 1004, Unable to get the Large property, at this part (from the Else statement): Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) Although I don't see exactly why the error occurs, I will add that I have now used a combination of what you and Chip wrote to get an answer that works fine for me. Still, I am curious as to what went wrong here -- if you don't mind looking further. (And what is CDate? Both you and Chip used it. Well, now I've found what it is via a web search, but I still don't quite see why the value in the date column isn't sufficient on its own so that we need to convert it to CDate. Maybe you can explain that part?) After I digested yours and Chip's I started thinking of a new approach. I don't know if it's better or worse, but it makes sense to me, so I'll ask what you guys think: what about just finding the 10th most recent date (via the same "WorksheetFunction.Large(...)" technique you both suggested) and then simply deleting cells that are smaller than that value? Well, that's what I've now coded. I still have to see about error-checking in case there aren't 10 dates, though. But wait -- if there aren't, I don't need to delete anything anyway, so I can just exit on such an error, I guess. Thanks so much, again. Sarah "Rick Rothstein" wrote in message ... I think this code will do what you want (change the worksheet reference in the With statement and the DateStartRow and DateColumn Const statements to suit your needs)... Sub CheckForSixItems() Dim X As Long Dim R As Range Dim LastRow As Long Dim LargeDateCells As Range Const DateStartRow As Long = 2 Const DateColumn As String = "B" With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row For X = 1 To 10 If LargeDateCells Is Nothing Then Set LargeDateCells = .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X))) Else Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) End If Next For Each R In Range(.Cells(DateStartRow, DateColumn), _ .Cells(LastRow, DateColumn)) If Intersect(R, LargeDateCells) Is Nothing Then R.Clear Next End With End Sub -- Rick (MVP - Excel) "Sarah H." wrote in message ... Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
First, let me say what a joy it was to read your response... you actually
read and analyze the code posted in response to your questions in an effort to understand how it works. All too often, the code we volunteers post on the newsgroups simply gets copied into a poster's code without them giving it a second thought. Okay, as to your pending questions (I see from your other response to this sub-thread that you got the error problem resolved)... And what is CDate? Both you and Chip used it. Well, now I've found what it is via a web search, but I still don't quite see why the value in the date column isn't sufficient on its own so that we need to convert it to CDate. Maybe you can explain that part?) You are probably right... CDate may not be needed. The thing I was concerned about (and didn't test any further) was that the underlying value for a date is an integer value and that Format might not automatically coerce it to a date before attempting to operate on it. In a quick test, it looks like the Format function does indeed perform the coercion (probably because d, m and y is being used in the "format string"), so you can probably safely remove the CDate function call (although explicitly performing the coercion can never be wrong). After I digested yours and Chip's I started thinking of a new approach. I don't know if it's better or worse, but it makes sense to me, so I'll ask what you guys think: what about just finding the 10th most recent date (via the same "WorksheetFunction.Large(...)" technique you both suggested) and then simply deleting cells that are smaller than that value? That may work for you depending on whether you can have duplicate dates and what you actually meant by 1. The method I used creates a range of the **first** 10 largest dates it finds and uses that to parse away the rest of the dates. Where this matters is with a set of dates like this... 1/1/2008, 1/1/2008, 1/1/2008, 1/1/2008, 1/1/2008, 1/2/2008, 1/3/2008, 1/4/2008, 1/5/2008, 1/6/2008, 1/7/2008, 1/8/2008... note there are 12 dates in the set with the first 5 of them being the same. My method will take the last 10 of them and delete the first two dates (even though they match other dates in the set) along with all earlier dates leaving 10 cells not deleted. Your method, because if finds the 10th largest date and deletes all previous dates will leave 12 cells not deleted. If you will not have duplicated dates, then either method will work the same and your proposed method would probably be the simpler one. -- Rick (MVP - Excel) "Sarah H." wrote in message ... Whoops, Rick -- my bad. Your code does work. The problem was that I tested it when I had fewer than 10 dates in the date column. I added an "On Error" statement and now it works. Still wondering about my other questions, though. Thanks again! Sarah ----------------- "Sarah H." wrote in message ... Rick, Your code helped me immensely. Thank you. (The name of the macro did not confuse me beyond a few seconds, but thank you for the follow-up clarification.) I think I mostly understand what you did. I have to admit that part of it didn't seem to work. I get a run-time error 1004, Unable to get the Large property, at this part (from the Else statement): Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) Although I don't see exactly why the error occurs, I will add that I have now used a combination of what you and Chip wrote to get an answer that works fine for me. Still, I am curious as to what went wrong here -- if you don't mind looking further. (And what is CDate? Both you and Chip used it. Well, now I've found what it is via a web search, but I still don't quite see why the value in the date column isn't sufficient on its own so that we need to convert it to CDate. Maybe you can explain that part?) After I digested yours and Chip's I started thinking of a new approach. I don't know if it's better or worse, but it makes sense to me, so I'll ask what you guys think: what about just finding the 10th most recent date (via the same "WorksheetFunction.Large(...)" technique you both suggested) and then simply deleting cells that are smaller than that value? Well, that's what I've now coded. I still have to see about error-checking in case there aren't 10 dates, though. But wait -- if there aren't, I don't need to delete anything anyway, so I can just exit on such an error, I guess. Thanks so much, again. Sarah "Rick Rothstein" wrote in message ... I think this code will do what you want (change the worksheet reference in the With statement and the DateStartRow and DateColumn Const statements to suit your needs)... Sub CheckForSixItems() Dim X As Long Dim R As Range Dim LastRow As Long Dim LargeDateCells As Range Const DateStartRow As Long = 2 Const DateColumn As String = "B" With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row For X = 1 To 10 If LargeDateCells Is Nothing Then Set LargeDateCells = .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X))) Else Set LargeDateCells = Union(LargeDateCells, _ .Columns(DateColumn). _ Find(CDate(WorksheetFunction. _ Large(.Columns(DateColumn), X)))) End If Next For Each R In Range(.Cells(DateStartRow, DateColumn), _ .Cells(LastRow, DateColumn)) If Intersect(R, LargeDateCells) Is Nothing Then R.Clear Next End With End Sub -- Rick (MVP - Excel) "Sarah H." wrote in message ... Hi, all, I'm stuck and hope you can offer some help. I know a bit about VBA programming but am having trouble writing code to do the following: I wish to delete all date values in a column of dates except for the most recent 10 entries. Can someone show me how? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting certain cells only
Rick,
Thanks a lot -- I appreciate the further explanation. Interesting as to the handling of multiple dates. Yes, in my case, it's not a problem, but I will keep the consideration in mind for this situation should it develop differently and also for further VBA uses. Sarah "Rick Rothstein" wrote in message ... First, let me say what a joy it was to read your response... you actually read and analyze the code posted in response to your questions in an effort to understand how it works. All too often, the code we volunteers post on the newsgroups simply gets copied into a poster's code without them giving it a second thought. Okay, as to your pending questions (I see from your other response to this sub-thread that you got the error problem resolved)... And what is CDate? Both you and Chip used it. Well, now I've found what it is via a web search, but I still don't quite see why the value in the date column isn't sufficient on its own so that we need to convert it to CDate. Maybe you can explain that part?) You are probably right... CDate may not be needed. The thing I was concerned about (and didn't test any further) was that the underlying value for a date is an integer value and that Format might not automatically coerce it to a date before attempting to operate on it. In a quick test, it looks like the Format function does indeed perform the coercion (probably because d, m and y is being used in the "format string"), so you can probably safely remove the CDate function call (although explicitly performing the coercion can never be wrong). After I digested yours and Chip's I started thinking of a new approach. I don't know if it's better or worse, but it makes sense to me, so I'll ask what you guys think: what about just finding the 10th most recent date (via the same "WorksheetFunction.Large(...)" technique you both suggested) and then simply deleting cells that are smaller than that value? That may work for you depending on whether you can have duplicate dates and what you actually meant by 1. The method I used creates a range of the **first** 10 largest dates it finds and uses that to parse away the rest of the dates. Where this matters is with a set of dates like this... 1/1/2008, 1/1/2008, 1/1/2008, 1/1/2008, 1/1/2008, 1/2/2008, 1/3/2008, 1/4/2008, 1/5/2008, 1/6/2008, 1/7/2008, 1/8/2008... note there are 12 dates in the set with the first 5 of them being the same. My method will take the last 10 of them and delete the first two dates (even though they match other dates in the set) along with all earlier dates leaving 10 cells not deleted. Your method, because if finds the 10th largest date and deletes all previous dates will leave 12 cells not deleted. If you will not have duplicated dates, then either method will work the same and your proposed method would probably be the simpler one. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Cells or Rows of Cells | Excel Discussion (Misc queries) | |||
deleting cells | New Users to Excel | |||
deleting unused cells / getting rid of inactive cells | Excel Discussion (Misc queries) | |||
Deleting #N/A from cells... | Excel Discussion (Misc queries) | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |