Deleting certain cells only

S

Sarah H.

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?
 
C

Chip Pearson

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("A1: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)
 
R

Rick Rothstein

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
 
R

Rick Rothstein

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.
 
S

Sarah H.

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
------------------
 
S

Sarah H.

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
 
S

Sarah H.

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
-----------------
 
R

Rick Rothstein

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)...

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).

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.
 
S

Sarah H.

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
 
Top