Copy cells if fit condition

Sure. There's a few ways you could approach this.

One that comes to mind is a For Each/Loop. For Each can only be applied to Collections and Arrays. Forunately, a 'Range' is considered a Collection.

Please Login or Register  to view this content.

For Each is good for breaking into VBA as it's sort of a literal expression. For each Range (cell) in A1:A6, this routine will check and see if it meets criteria, and then paste it into the next blank cell in C. Put something in C3 and the data will autofill below.

After the procedure loops through the whole range, it outputs via MsgBox.

As described earlier, a Range can be a cell Range("A1") or a group of cells Range("A1:A6").

Range(Range("C4"),Range("C" & Rows.Count).End(xlUp)) means C4 to the last cell in C. We count these cells and we also average them.

Another alternative:

Please Login or Register  to view this content.

or,

Please Login or Register  to view this content.

Cheers,
berlan

Since you mention you had considered conditional formatting: you could avoid all VBA for the project as far as you've described (though I suppose your project could have more pieces). If you don't mind having the desired values shown in C and a dash otherwise, just
C1=if(and(a1>3,a1<6),a1,"-")
and copy down. Then C7 could be SUM or AVERAGE (or COUNT, but perhaps not COUNTA) and the dashes would not foul that up. (The reason for the dashes is so that it would be apparent that those cells in C are being used, possibly simplifying maintenance down the road.)

Personally, if I went with the above, I'd parameterize the 3 and 6, maybe by having them in their own cells, and named as ranges.

I don't mean to diminish the code solutions above and your needs may be such that you should use them. However as a matter of philosophy and pragmatism and a few other reasons, I often avoid code when noncomplex worksheet functions simply get the result. I say that even though I loooove coding personally, and in many situations I proceed immediately to code, definitely when looping or certain branching is needed, or maybe even if it's more compact to use code. I code a real lot to use InputBox and to process buttons and events. However there are often tradeoffs by using VBA in the long run when others have to maintain a workbook so often I have none.

There's not a strict answer as to whether VBA should be preferred or avoided depending on several factors, but I thought I'd make the suggestion that in this case you could just "stay on the sheets." Of course, for any individual, their "shop", the ultimate users, and the application, "your mileage may vary."

No offense to anyone, especially the repliers above who worked to provide good answers; yes I know I'm in a VBA forum!!

That's fine berlan, but just to again add perspective, array formulas do add complexity and are more difficult to maintain in my long experience; for example, many times a workbook user either forgot to go shift-enter, or didn't even know about shift-enter. Filters often cause even more drama - for example, inserting/deleting rows while filtered is an adventure, or simply forgetting that you're looking at a subset rather than full unfiltered data can cause false trails and time burn. Again, whether to use them or not depends on numerous factors, namely who will use and maintain the workbook. Filter and arrays are powerful. They're also compact, so in that sense, they're easier to maintain. I'm just saying that there may be a tradeoff. Moreover, for this problem, the formula I suggested is extremely simple to follow and maintain, vs. VBA which would be overkill IMO.

Another point: over time I've concluded that, when you use filters, an explicit indication on a worksheet (e.g. a caption, or just a prominently visible cell, but more than just a comment) can save immense time down the road, so when you look at a sheet you immediately know there's filtering and array formula. I can recall a lot of time that I and others have wasted because we didn't immediately realize that there was filtering and array formulas.

Agreed on all. To clarify, yes, I was referring to autofilter and the "advanced filter." Thanks for your clarification as well.