Sorting Certain Parts of Data

[Guest]

Ok, here's the issue: I have a data set, single column, with some spaces in between the data. It looks something like this-

Data1
Data2

Data3
Data4
Data5
So on and so forth. Is there a way that I can grab only the last piece of data before each break, put them all in another column, with no spaces between? For the example above, it would look like:

Data2
Data5

Any help at all would be great. Thanks in advance.

 

[kkknie]

Here's some code that would do it:


Code
-------------------
Sub test()

Dim r As Range
Dim iRow As Long

iRow = 1
For Each r In Range("A1:A" & Range("A65536").End(xlUp).Row + 1)

If r.Value = "" Then
Range("B" & iRow).Value = Range("A" & r.Row - 1).Value
If Range("A" & r.Row + 1).Value = "" Then Exit For 'exit if two blanks found
iRow = iRow + 1
End If

Next

End Su

 

[Ron de Bruin]

Try this macro in a test workbook

The data is in column A in this example and I
assume that there are only single blank rows between your data

Sub test()
Dim cell As Range
Dim rw As Long
rw = 0
On Error Resume Next
For Each cell In Columns("A").SpecialCells(xlCellTypeBlanks)
rw = rw + 1
Cells(rw, "B").Value = cell.Offset(-1, 0).Value
Next
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

Ok, here's the issue: I have a data set, single column, with some spaces in between the data. It looks something like this-

Data1
Data2

Data3
Data4
Data5
So on and so forth. Is there a way that I can grab only the last piece of data before each break, put them all in another column,

with no spaces between? For the example above, it would look like:

 

[KKindle]

I'd use brute force ... create a second column that has
trues or falses depending on whether the data satisfy your
conditions and then filter on the true's

If your data starts in A2, then the formula you want in B2
is =AND(NOT(ISBLANK(A2)),ISBLANK(A3)).

Copy this formula down column B to the end of your column
of data ... you will have trues and falses in column B
with the trues being the data that you want. Now filter
your data on column B being true.

-----Original Message-----
Ok, here's the issue: I have a data set, single column,

with some spaces in between the data. It looks something
like this-

Data1
Data2

Data3
Data4
Data5
So on and so forth. Is there a way that I can grab only

the last piece of data before each break, put them all in
another column, with no spaces between? For the example
above, it would look like: