finds and concatenate
 

Hi all,

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

What i want is that a macro which upon finding a cell with date forma
, 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 dat
and then a blank line, what i want is that it selects all those cell
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 blan
line.
any help is appreciate

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

finds and concatenate
 

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/

finds and concatenate
 

thanks bob!

ok, can we have a macro which upon finding the date format in a cell
clears not deletes the cells below it till a blank line appears belo
those cells

like if a date cell is A2, it clears A3&B3 in case if there is only on
row of data below date cell

in case there are more cells , it deletes all

thank u so much for ur hel

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

finds and concatenate
 

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+1, "A"), Cells(j - 1, "A")).ClearContente
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
...
thanks bob!

ok, can we have a macro which upon finding the date format in a cell ,
clears not deletes the cells below it till a blank line appears below
those cells

like if a date cell is A2, it clears A3&B3 in case if there is only one
row of data below date cell

in case there are more cells , it deletes all

thank u so much for ur help


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