Jumping in a For ..Next Loop

[Bertrand]

XL2002

My basic macro is:-

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10
newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

I.E. I want to concatenate the text in Column A rows 1 - 10 and put the
result in L1
Then do the same for column B and put the result in L2 and so on

However, I want to ignore any blank cells - i.e. do not insert any ""s into
the strings.

I've tried inserting what I would call a forced jump [as below in caps]

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10

IF CELLS(R,C) = "" THEN NEXT R

newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

in other words ; if the cell concerned is blank then jump to the next row
but this doesn't work.

I thought that it could be that the forced NEXT R instruction is putting R
out of its For...Next range but it happens even when R is below 10

What am I doing wrong?

Thanks

Bertrand

[Charlie]

For R = 1 to 10
IF CELLS(R,C) <> "" THEN newtext = newtext & Cells(R,C)
next R

"Bertrand" wrote:

> XL2002
>
> My basic macro is:-
>
> Sub ABC()
>
> Newtext = ""
> For C = 1 to 5
> For R = 1 to 10
> newtext = newtext & Cells(R,C)
> next R
> Cells(r,12) = newtext
> newtext = ""
> next C
>
> End
>
> I.E. I want to concatenate the text in Column A rows 1 - 10 and put the
> result in L1
> Then do the same for column B and put the result in L2 and so on
>
> However, I want to ignore any blank cells - i.e. do not insert any ""s into
> the strings.
>
> I've tried inserting what I would call a forced jump [as below in caps]
>
> Sub ABC()
>
> Newtext = ""
> For C = 1 to 5
> For R = 1 to 10
>
> IF CELLS(R,C) = "" THEN NEXT R
>
> newtext = newtext & Cells(R,C)
> next R
> Cells(r,12) = newtext
> newtext = ""
> next C
>
> End
>
> in other words ; if the cell concerned is blank then jump to the next row
> but this doesn't work.
>
> I thought that it could be that the forced NEXT R instruction is putting R
> out of its For...Next range but it happens even when R is below 10
>
> What am I doing wrong?
>
> Thanks
>
> Bertrand
>
>
>
>
>
>
>
>
>