Collating Collumn
 

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

Collating Collumn
 

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

Collating Collumn
 

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

Collating Collumn
 

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" wrote in message
oups.com...
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

Collating Collumn
 

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" wrote in message
oups.com...
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