Transpose column

[crobe]

I have a sheet that is an export from a crystal report.
There are blanks in the column that seperate the data sets.

I am trying to find a way to concatenate everything in the column between blank cells.

There are some 30,000 rows in one sheet and there are several dumps so I would really be interested in finding a better way then copy/paste special/transpose 90,000 times.

The sheet looks like this

COLUMN B COLUMN C
05 This will not be completed per ***** ******. -Cagnina




07 CANCELLED ON MAY 23RD 2001, BECAUSE IT HAS BEEN OPEN FOR
07 OVER TWO YEARS!!! WILL RE-SUBMIT. JIM *****




05 checked with the City. They have no
05 problem with the building coming down. 5/99
05 Will give this to ********.
05 Picked up heater and dropped off at GCO. 8/24/99 C. Smith
05 Close per yacono create new work order if needed.


Any help would be great.
Thanks

 

[PHV]

A starting point:
With Columns("B:C")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Hi,

I assume that columns B & C are the ONLY columns containing data. Do 05 and 07 have any significance?

This code puts the concatenated output in column E...

Sub Main()
   rOut = 1
   With ActiveSheet
      r = .UsedRange.Row
      rLast = .Cells(.Cells.Rows.Count, "C").End(xlUp).Row
      s = .Cells(r, "C").Value
      Do While r <= rLast
         If .Cells(r + 1, "C").Value = "" Then
            r = .Cells(r, "C").End(xlDown).Row
            .Cells(rOut, "E").Value = s
            rOut = rOut + 1
            s = ""
         Else
            r = r + 1
         End If
         s = s & .Cells(r, "C").Value
      Loop
   End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]