How to use macros to copy a range of cells which can exclude some cells which I didn't want to be copied?

For example if I have data like this:

Eggs
NIL
Ham
Bacon
NIL
Cheese
Salmon
Chicken
Turkey
NIL


I want to use macros to copy the cells which will exclude cells which
has NIL... to give me this:

Eggs
Ham
Bacon
Cheese
Salmon
Chicken
Turkey

How can i do it? The only thing I know here is to use
Range("A1:A10").Copy........ which will always include NIL......

Put your data in column A and run this tiny macro:

Sub novice()
j = 1
For i = 1 To 10
If Cells(i, 1) = "NIL" Then
Else
Cells(j, 2) = Cells(i, 1)
j = j + 1
End If
Next
End Sub


and your copy will be in column B

Have a good day
--
Gary''s Student


"excelnovice" wrote:

> For example if I have data like this:
>
> Eggs
> NIL
> Ham
> Bacon
> NIL
> Cheese
> Salmon
> Chicken
> Turkey
> NIL
>
>
> I want to use macros to copy the cells which will exclude cells which
> has NIL... to give me this:
>
> Eggs
> Ham
> Bacon
> Cheese
> Salmon
> Chicken
> Turkey
>
> How can i do it? The only thing I know here is to use
> Range("A1:A10").Copy........ which will always include NIL......
>
>

On 24 Sep 2005 11:51:20 -0700, "excelnovice"
<[email protected]> wrote:

>For example if I have data like this:
>
>Eggs
>NIL
>Ham
>Bacon
>NIL
>Cheese
>Salmon
>Chicken
>Turkey
>NIL
>
>
>I want to use macros to copy the cells which will exclude cells which
>has NIL... to give me this:
>
>Eggs
>Ham
>Bacon
>Cheese
>Salmon
>Chicken
>Turkey
>
>How can i do it? The only thing I know here is to use
>Range("A1:A10").Copy........ which will always include NIL......


The easiest way without macros is to filter your list using the 'does
not contain' "NIL" option, highlight the range, Copy and then Paste
somewhere else.

If you really want to use a macro then you could get it to do the same
thing.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________