How to copy only some rows from range based on condition?

[Guest]

I am giving myself a crash course in VBA programming using
Walkenbach's book. Hopefully I can figure this out for myself
in due time. But as a double-check and perhaps for expediency
(learn by example), I would appropriate it if someone would
show me a VBA sub (executed using alt-F8) to do the following.

I have a worksheet with 10200 daily stock prices (date, open,
high, low, close). I want to build worksheet with the last stock
prices of each month. (Note: I have good reasons for not
simply getting this from Yahoo or other online service.)

The pseudocode for the VBA sub that I have in mind is (better
ideas are welcomed):

' data is ordered from most recent to oldest date
' skip 1st month (might be partial)
lastMonth = month(first cell in selected range)
for each cell in selected range
if month(cell) <> lastMonth then
copy 5 contiguous columns (same row) to the right
to new row in another (specified) worksheet
lastMonth = month(cell)
end if
next cell

Note: Can "selected range" be just the date column, or must
it encompass all 5 columns in the 10200 rows? Even if can be
just the date column, is there any advantage (in execution
time or ease of programming) to encompassing all 5 columns?

PS: If you are kind enough to post a prototype VBA, there is
no need or expectation to test it. I can do that. I am just
hoping for a leg-up on relevant nuances of the VBA language.

TIA.

 

[Tom Ogilvy]

The advanced filter does this in one command. You would be well served to
use the power that is inherent in Excel.

Turn on the macro recorder and apply an advanced filter.

Turn off the macro recorder and modify the code to suit your needs.

here is an example from a post a few prior to yours:

Range("hist_data").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Range("job_crit"), _
copytorange:=Range("hist_out"), _
unique:=False

If you don't know how to apply an advanced filter in Excel, go to Debra
Dalgleish's site and look in the index for advanced filter.

http://www.contextures.com/tiptech.html

Even if learning VBA, is isn't usually useful to reinvent the wheel.


If you are using John's book, you should quickly note that the concept of
selecting a range is dismissed very early.

 

[Guest]

The advanced filter does this in one command. You would
be well served to use the power that is inherent in Excel.

I agree. Thanks for the pointer. I will see if it does the job.
(Frankly, I would be very pleasantly surprised.)

Even if learning VBA, is isn't usually useful to reinvent the wheel.

I strongly disagree. Back when I took CS 1 (et al) 38 years
ago, that is exactly how we learned programming. I cannot
imagine that things have changed. In fact, to that end, I will
continue to learn a VBA solution just, if only for my edification
about VBA. It has already been very educational.

So I hope that others will be kind enough to continue discussion
in this thread. I no longer need a solution -- I think I have a
handle on it. But I am sure that I will have questions about
this-and-that alternative programming.

TIA.