Find Month in a cell

[visible2you]

In Col C, Date is given like this:-

1-Jun-11
2-Jun-11
3-Jun-11
4-Jun-11
5-Jun-11
6-Jun-11
1-Aug-11
2-Aug-11
3-Aug-11
4-Aug-11
5-Aug-11
6-Aug-11

I want to select rectangular range starting from Col A which includes only "August" month. What will be modified version of this program: -

Sub selcol()

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

End Sub

 

[SkipVought]

Hi,

that would be where the Month(DateValue) = 8

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

I want to select rectangular range which includes only "August" month. Range must start from Col A.

 

[SkipVought]

What do you intend to do with just that range? Don't you also have dates in other months that you may need to look at? It is a highly unusual way to construct a range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

I want to extract, paste and keep only selected range having "august" month for analysis purpose.

 

[SkipVought]

why vba to do that?

I would very seldom use that technique. It is usually better to reference the entire table or list.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

Excel Formula can be deleted by user by mistake. VBA program has less probability of being deleted by accident while working on sheet.

 

[SkipVought]

What code do you have so far?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

you can define this RANGE in Defined Names, where...
[tt]
X1: 8/1/2011
Y1: 9/1/2011
DTE: your Date Range

Defined Name Range Formula:
OFFSET(DTE,MATCH(X1,DTE,1)-1,0,COUNTIFS(DTE,">="&X1,DTE,"<"&Y1),1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]