I want to check down a column of cells, some of which may be blank, I want to
gnore the blank ones and print only the contents (in another cell) those with
a number in them.

eg
A B
1 1234
2 1234
3

in the example above I want to ignore A1 & A3 but print the result of A2 to
B1

If you don't wish to use filters and copy/pastes the simplest method is to
use a macro to do this:

Sub movedata()
numrows = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B1").Select
For myloop = 1 To numrows
If Cells(myloop, 1).Value < "" Then
ActiveCell.Value = Cells(myloop, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next myloop
End Sub
--
Kevin Ciccone


"Rollo Tomasi" wrote:

I want to check down a column of cells, some of which may be blank, I want to
gnore the blank ones and print only the contents (in another cell) those with
a number in them.

eg
A B
1 1234
2 1234
3

in the example above I want to ignore A1 & A3 but print the result of A2 to
B1

Thanks Dom_Ciccone

Maybe I should re-phrase my query.
in a particular column I have 30 or cells which may or may not have a job no
eg :

input output
blank, 45678
blank, 12345
45678, 34567
blank,
12345,
blank,
34567
etc.

i.e. I want to ignore the blank cells,but paste the data from cells which DO
contain a number to a cell on another column, filling the column so that it
contains ONLY cells with a number but I want this to happen 'automatically'
as soon as any relevant data (a number in this case) is entered into a
currently blank cell. it is not something I want to have to run each time I
add new data.

unfortunately the format of my 'example' didn't translate on this website
very well.


"Dom_Ciccone" wrote:

If you don't wish to use filters and copy/pastes the simplest method is to
use a macro to do this:

Sub movedata()
numrows = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B1").Select
For myloop = 1 To numrows
If Cells(myloop, 1).Value < "" Then
ActiveCell.Value = Cells(myloop, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next myloop
End Sub
--
Kevin Ciccone


"Rollo Tomasi" wrote:

I want to check down a column of cells, some of which may be blank, I want to
gnore the blank ones and print only the contents (in another cell) those with
a number in them.

eg
A B
1 1234
2 1234
3

in the example above I want to ignore A1 & A3 but print the result of A2 to
B1

Rollo

Try this event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
rng.Value = Excel.Range("A" & n).Value
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste into that sheet
module.

As you enter values in column A they will be copied to column B as per your
example.


Gord Dibben MS Excel MVP


On Thu, 17 May 2007 06:00:01 -0700, Rollo Tomasi
wrote:

Thanks Dom_Ciccone

Maybe I should re-phrase my query.
in a particular column I have 30 or cells which may or may not have a job no
eg :

input output
blank, 45678
blank, 12345
45678, 34567
blank,
12345,
blank,
34567
etc.

i.e. I want to ignore the blank cells,but paste the data from cells which DO
contain a number to a cell on another column, filling the column so that it
contains ONLY cells with a number but I want this to happen 'automatically'
as soon as any relevant data (a number in this case) is entered into a
currently blank cell. it is not something I want to have to run each time I
add new data.

unfortunately the format of my 'example' didn't translate on this website
very well.


"Dom_Ciccone" wrote:

If you don't wish to use filters and copy/pastes the simplest method is to
use a macro to do this:

Sub movedata()
numrows = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B1").Select
For myloop = 1 To numrows
If Cells(myloop, 1).Value < "" Then
ActiveCell.Value = Cells(myloop, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next myloop
End Sub
--
Kevin Ciccone


"Rollo Tomasi" wrote:

I want to check down a column of cells, some of which may be blank, I want to
gnore the blank ones and print only the contents (in another cell) those with
a number in them.

eg
A B
1 1234
2 1234
3

in the example above I want to ignore A1 & A3 but print the result of A2 to
B1

Thanks Gord

I have the gist of it now and can tinker with it to get the results I need !


"Gord Dibben" wrote:

Rollo

Try this event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
rng.Value = Excel.Range("A" & n).Value
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste into that sheet
module.

As you enter values in column A they will be copied to column B as per your
example.


Gord Dibben MS Excel MVP


On Thu, 17 May 2007 06:00:01 -0700, Rollo Tomasi
wrote:

Thanks Dom_Ciccone

Maybe I should re-phrase my query.
in a particular column I have 30 or cells which may or may not have a job no
eg :

input output
blank, 45678
blank, 12345
45678, 34567
blank,
12345,
blank,
34567
etc.

i.e. I want to ignore the blank cells,but paste the data from cells which DO
contain a number to a cell on another column, filling the column so that it
contains ONLY cells with a number but I want this to happen 'automatically'
as soon as any relevant data (a number in this case) is entered into a
currently blank cell. it is not something I want to have to run each time I
add new data.

unfortunately the format of my 'example' didn't translate on this website
very well.


"Dom_Ciccone" wrote:

If you don't wish to use filters and copy/pastes the simplest method is to
use a macro to do this:

Sub movedata()
numrows = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B1").Select
For myloop = 1 To numrows
If Cells(myloop, 1).Value < "" Then
ActiveCell.Value = Cells(myloop, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next myloop
End Sub
--
Kevin Ciccone


"Rollo Tomasi" wrote:

I want to check down a column of cells, some of which may be blank, I want to
gnore the blank ones and print only the contents (in another cell) those with
a number in them.

eg
A B
1 1234
2 1234
3

in the example above I want to ignore A1 & A3 but print the result of A2 to
B1