Hi All,

I need a way of collating a list so that I am left with a solid list,
my column has formulas in that are blank if the return value is false,
or display the return value if it is true. So it will look something
like the below

Peter Pan
(blank)
(blank)
(blank)
(blank)
Captain Hook
(blank)
(blank)
Snow White
(blank)
(blank)
(blank)

And I would really like to have something behind a button that would
put the names all together in another column with no blank spaces
between them
(like the below returned from the above example_

Peter Pan
Captain Hook
Snow White

Does this make sense? and is it possible? Can you use code to find out
if the formula in the cell has returned blank for false or value for
true?

Many thanks in advance

Duncan

This little routine will delete the rows where the cells or blank ie =""

Sub deleteblankrows()
Dim ptr As Long
Dim i As Long, lastrow As Long

With ActiveCell.CurrentRegion.Columns(1)
lastrow = .Rows.Count
For i = lastrow To 1 Step -1
If .Cells(i, 1).Value = "" Then
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With
End Sub


Hope this helps
Martin Fishlock


"Duncan" wrote:

> Hi All,
>
> I need a way of collating a list so that I am left with a solid list,
> my column has formulas in that are blank if the return value is false,
> or display the return value if it is true. So it will look something
> like the below
>
> Peter Pan
> (blank)
> (blank)
> (blank)
> (blank)
> Captain Hook
> (blank)
> (blank)
> Snow White
> (blank)
> (blank)
> (blank)
>
> And I would really like to have something behind a button that would
> put the names all together in another column with no blank spaces
> between them
> (like the below returned from the above example_
>
> Peter Pan
> Captain Hook
> Snow White
>
> Does this make sense? and is it possible? Can you use code to find out
> if the formula in the cell has returned blank for false or value for
> true?
>
> Many thanks in advance
>
> Duncan
>
>

Hi Martin,

No sorry i maybe didnt explain properly, My column is not really blank,
its just 'blank' if the formula is false, i.e the formula running down
it is

=IF(K4="AYE!!!",MID(A3,1,17),"")
=IF(K5="AYE!!!",MID(A4,1,17),"")
=IF(K6="AYE!!!",MID(A5,1,17),"")
=IF(K7="AYE!!!",MID(A6,1,17),"")
=IF(K8="AYE!!!",MID(A7,1,17),"")
=IF(K9="AYE!!!",MID(A8,1,17),"")
etc
etc

And also, I dont want to delete the blank ones as I will use this again
but with differant data, I just want to copy out the ones with a true
answer (the true result) and paste them all together in a list.

Many thanks

Duncan



Martin Fishlock wrote:
> This little routine will delete the rows where the cells or blank ie =""
>
> Sub deleteblankrows()
> Dim ptr As Long
> Dim i As Long, lastrow As Long
>
> With ActiveCell.CurrentRegion.Columns(1)
> lastrow = .Rows.Count
> For i = lastrow To 1 Step -1
> If .Cells(i, 1).Value = "" Then
> .Cells(i, 1).EntireRow.Delete
> End If
> Next i
> End With
> End Sub
>
>
> Hope this helps
> Martin Fishlock
>
>
> "Duncan" wrote:
>
> > Hi All,
> >
> > I need a way of collating a list so that I am left with a solid list,
> > my column has formulas in that are blank if the return value is false,
> > or display the return value if it is true. So it will look something
> > like the below
> >
> > Peter Pan
> > (blank)
> > (blank)
> > (blank)
> > (blank)
> > Captain Hook
> > (blank)
> > (blank)
> > Snow White
> > (blank)
> > (blank)
> > (blank)
> >
> > And I would really like to have something behind a button that would
> > put the names all together in another column with no blank spaces
> > between them
> > (like the below returned from the above example_
> >
> > Peter Pan
> > Captain Hook
> > Snow White
> >
> > Does this make sense? and is it possible? Can you use code to find out
> > if the formula in the cell has returned blank for false or value for
> > true?
> >
> > Many thanks in advance
> >
> > Duncan
> >
> >

Sub CollateAtoB()
Dim LRow As Long, NxtRow As Long
Dim Rng As Range, c As Range

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
NxtRow = 1
Set Rng = Range("A1:A"& LRow)
For Each c in Rng
If c.Value = "" Then
' do nothing
Else
Range("B"& NxtRow).Value = c.Value
NxtRow = NxtRow + 1
End If
Next
End Sub

This is untested, but should do what you want in column B.

Mike F
"Duncan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
>
> I need a way of collating a list so that I am left with a solid list,
> my column has formulas in that are blank if the return value is false,
> or display the return value if it is true. So it will look something
> like the below
>
> Peter Pan
> (blank)
> (blank)
> (blank)
> (blank)
> Captain Hook
> (blank)
> (blank)
> Snow White
> (blank)
> (blank)
> (blank)
>
> And I would really like to have something behind a button that would
> put the names all together in another column with no blank spaces
> between them
> (like the below returned from the above example_
>
> Peter Pan
> Captain Hook
> Snow White
>
> Does this make sense? and is it possible? Can you use code to find out
> if the formula in the cell has returned blank for false or value for
> true?
>
> Many thanks in advance
>
> Duncan
>

Mike,

Brilliant.

Thank you

Duncan


Mike Fogleman wrote:
> Sub CollateAtoB()
> Dim LRow As Long, NxtRow As Long
> Dim Rng As Range, c As Range
>
> Lrow = Cells(Rows.Count, "A").End(xlUp).Row
> NxtRow = 1
> Set Rng = Range("A1:A"& LRow)
> For Each c in Rng
> If c.Value = "" Then
> ' do nothing
> Else
> Range("B"& NxtRow).Value = c.Value
> NxtRow = NxtRow + 1
> End If
> Next
> End Sub
>
> This is untested, but should do what you want in column B.
>
> Mike F
> "Duncan" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi All,
> >
> > I need a way of collating a list so that I am left with a solid list,
> > my column has formulas in that are blank if the return value is false,
> > or display the return value if it is true. So it will look something
> > like the below
> >
> > Peter Pan
> > (blank)
> > (blank)
> > (blank)
> > (blank)
> > Captain Hook
> > (blank)
> > (blank)
> > Snow White
> > (blank)
> > (blank)
> > (blank)
> >
> > And I would really like to have something behind a button that would
> > put the names all together in another column with no blank spaces
> > between them
> > (like the below returned from the above example_
> >
> > Peter Pan
> > Captain Hook
> > Snow White
> >
> > Does this make sense? and is it possible? Can you use code to find out
> > if the formula in the cell has returned blank for false or value for
> > true?
> >
> > Many thanks in advance
> >
> > Duncan
> >