check a row ignore blank cells print only those with text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rollo Tomasi - 17 May 2007 12:33 GMT

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

Reply to this Message

Dom_Ciccone - 17 May 2007 13:33 GMT

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

Signature

Kevin Ciccone

> 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
[quoted text clipped - 8 lines]
> in the example above I want to ignore A1 & A3 but print the result of A2 to
> B1

Reply to this Message

Rollo Tomasi - 17 May 2007 14:00 GMT

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.

> If you don't wish to use filters and copy/pastes the simplest method is to
> use a macro to do this:
[quoted text clipped - 22 lines]
> > in the example above I want to ignore A1 & A3 but print the result of A2 to
> > B1

Reply to this Message

Gord Dibben - 17 May 2007 17:22 GMT

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

>Thanks Dom_Ciccone
>
[quoted text clipped - 48 lines]
>> > in the example above I want to ignore A1 & A3 but print the result of A2 to
>> > B1

Reply to this Message

Rollo Tomasi - 18 May 2007 08:32 GMT

Thanks Gord

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

> Rollo
>
[quoted text clipped - 75 lines]
> >> > in the example above I want to ignore A1 & A3 but print the result of A2 to
> >> > B1

Reply to this Message