Vikram,

Not foolproof, but

Sub GetData()
Dim cLastRow
Dim i As Long
Dim j As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "A").NumberFormat Like "*d*" Then
For j = i + 1 To cLastRow
If Cells(j, "A").Value = "" Then
Exit For
End If
Next j
Range(Cells(i, "A"), Cells(j - 1, "A")).Copy Range("B1")
i = i + j - 1
End If
Next i
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"vikram " wrote in message
...
Hi all,

Column A has a cell which contains date format and in column A there
are about 50 cells which contains date format .

What i want is that a macro which upon finding a cell with date format
, selects all the cells below it until a blank line below them comes.

like if in cell A5 there is date format and 6 cells below it has data
and then a blank line, what i want is that it selects all those cells
which are below the date cell and pastes them in the column B

and dates may be in cell A2, A8, A20 etc which may vary.
sometimes below the date formt cell there is no data, just a blank
line.
any help is appreciated


---
Message posted from http://www.ExcelForum.com/