Solved: Select defined range

[justdriving]

This is Col C

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

How to select a range starting from Col A to have only "August" month.

Can you please help me to modify this program: -


Sub seecol()

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

End Sub


Please help me to select a rectangular range to include only "August" month. Range starting from Col A.

[p45cal]

Makes lots of assumptions:
FR = Evaluate("MIN(IF(MONTH(C:C)=8,ROW(C:C)))")
LR = Evaluate("Max(IF(MONTH(C:C)=8,ROW(C:C)))")
Range("A" & FR & ":E" & LR).Select
Works on active sheet, assumes contiguous August cells, ignores year.
Will be slow 'cos it works on entire column C, but will speed up a lot if you replace C:C with likes of C1:C1000:
FR = Evaluate("MIN(IF(MONTH(C1:C1000)=8,ROW(C1:C1000)))")
LR = Evaluate("Max(IF(MONTH(C1:C1000)=8,ROW(C1:C1000)))")
Range("A" & FR & ":E" & LR).Select

These will be fooled with plain numbers such as 236 which can represent a date in Aug 1900.

Another method:
LastRowOnSheet = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
For rw = 1 To LastRowOnSheet
If IsDate(Cells(rw, 3).Value) And Month(Cells(rw, 3).Value) = 8 Then
FR = rw
Exit For
End If
Next rw
For rw = rw + 1 To LastRowOnSheet
If Not (IsDate(Cells(rw, 3).Value)) Or Month(Cells(rw, 3)) <> 8 Then
LR = rw - 1
Exit For
End If
Next rw
Range("A" & FR & ":E" & LR).Select
Again, contiguous august cells etc.

[justdriving]

I want to thank you. I need another help. If LR will be last row in Col C (not containing Aug month), then, how to find that?

[justdriving]

After going through quoted program, it was wonderful program. I have made few modifications to suit my requirements: -


Sub tr()
LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
LastRow = ActiveSheet.Cells(1, LastCol).End(xldown).Row
For rw = 1 To LastRow
If IsDate(Cells(rw, 3).Value) And Month(Cells(rw, 3).Value) = 8 Then
FR = rw 'if both condition before and after "AND" to be true
Exit For
Else
FR = 1
End If
Next rw
For rw = rw + 1 To LastRow
If Not (IsDate(Cells(rw, 3).Value)) Or Month(Cells(rw, 3)) <> 8 Then
LR = rw - 1
Exit For
Else
LR = LastRow
End If
Next rw
Range("A" & FR, Cells(LR, LastCol)).Select
End Sub

Many thanks, again. Looking to be friend with you.