Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells that
have data. (It will be text data)

These cells are then copied to the clipboard for pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub

Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells that
have data. (It will be text data)

These cells are then copied to the clipboard for pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub

Thanks Tom,
Regards,
Richard

-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells that
have data. (It will be text data)

These cells are then copied to the clipboard for pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.

Hello Tom,
Just tried the code but a bit of a problem. Crashes at
line commencing "set rng". Error Msg 1004, Application-
defined or Object-defined error.

Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard


-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells that
have data. (It will be text data)

These cells are then copied to the clipboard for pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.

I can't reproduce it - even on an empty spreadsheet:

set rng = Range(Range("T9"),Range("T111").End(xlup))
? rng.Address
$T$5:$T$9


--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello Tom,
Just tried the code but a bit of a problem. Crashes at
line commencing "set rng". Error Msg 1004, Application-
defined or Object-defined error.

Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard


-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells that
have data. (It will be text data)

These cells are then copied to the clipboard for pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.

Thanks Tom,
I'll have a play around with it and get back to you. I
can't see what is wrong but it certainly generates the
error message on mine. I'll copy my formulas and code
relating only to this section to an empty sheet and see
what happens.

Regards,
Richard

-----Original Message-----
I can't reproduce it - even on an empty spreadsheet:

set rng = Range(Range("T9"),Range("T111").End(xlup))
? rng.Address
$T$5:$T$9


--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello Tom,
Just tried the code but a bit of a problem. Crashes at
line commencing "set rng". Error Msg 1004, Application-
defined or Object-defined error.

Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard


-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in
them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells
that
have data. (It will be text data)

These cells are then copied to the clipboard for
pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto
Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto
Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.



.

Hi Tom,
I've now had a chance to try a condensed version on an
empty sheet and it still crashes at the same line with the
same message.

This is what I've done.
A B D F H
Data Formulas Extracted asText Sorted Text Pasted Text
F2:F5 H2:H5
Blue Blue Blue Blue Blue
Red Blue Blue
Blue Blue Blue Blue Blue
Green Blue Blue
Blue Blue Blue
Blue Blue Blue
Red
Red
Green

Formula =if(A2="Blue","Blue","")

What I want is just F2:F5 (NOT F2:F10) so I can paste to
H2:H5 (excludes any blanks).

Can't see why it won't run properly.
If you get a chance could you have another look at it.

Thanks,
Richard


Sub TestToCopy()
ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R2C3:R10C3"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Application.Goto Reference:="R2C6:R10C6"
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

Set rng = Range(Range("F2"), Range("F10").End(x1up))
'CRASHES
HERE. 1004 Application or Object defined error.
rng.Copy
Range("A1").Select
End Sub




-----Original Message-----
I can't reproduce it - even on an empty spreadsheet:

set rng = Range(Range("T9"),Range("T111").End(xlup))
? rng.Address
$T$5:$T$9


--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello Tom,
Just tried the code but a bit of a problem. Crashes at
line commencing "set rng". Error Msg 1004, Application-
defined or Object-defined error.

Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard


-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in
them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells
that
have data. (It will be text data)

These cells are then copied to the clipboard for
pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto
Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto
Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.



.

Set rng = Range(Range("F2"), Range("F10").End(x1up))

your problem is you are using a constant x1up and it should be xlup using
the letter "L" rather than the number 1 as you are doing.

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Hi Tom,
I've now had a chance to try a condensed version on an
empty sheet and it still crashes at the same line with the
same message.

This is what I've done.
A B D F H
Data Formulas Extracted asText Sorted Text Pasted Text
F2:F5 H2:H5
Blue Blue Blue Blue Blue
Red Blue Blue
Blue Blue Blue Blue Blue
Green Blue Blue
Blue Blue Blue
Blue Blue Blue
Red
Red
Green

Formula =if(A2="Blue","Blue","")

What I want is just F2:F5 (NOT F2:F10) so I can paste to
H2:H5 (excludes any blanks).

Can't see why it won't run properly.
If you get a chance could you have another look at it.

Thanks,
Richard


Sub TestToCopy()
ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto Reference:="R2C3:R10C3"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Application.Goto Reference:="R2C6:R10C6"
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

Set rng = Range(Range("F2"), Range("F10").End(x1up))
'CRASHES
HERE. 1004 Application or Object defined error.
rng.Copy
Range("A1").Select
End Sub




-----Original Message-----
I can't reproduce it - even on an empty spreadsheet:

set rng = Range(Range("T9"),Range("T111").End(xlup))
? rng.Address
$T$5:$T$9


--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello Tom,
Just tried the code but a bit of a problem. Crashes at
line commencing "set rng". Error Msg 1004, Application-
defined or Object-defined error.

Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard


-----Original Message-----
Application.ScreenUpdating = False

Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Richard" wrote in message
...
Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9:P110 to T9
The range will include some cells with nothing in
them
that is:- no formulas or text.

Then I want to extract from T9:T110 JUST the cells
that
have data. (It will be text data)

These cells are then copied to the clipboard for
pasting
into another application.

I keep getting many blank cells at the bottom which I
don't want.
How do I just get the cells with text in them.

Thanks,
Richard

This is my code

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Application.Goto
Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto
Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy

Range("B4").Select
Application.ScreenUpdating = True
End Sub


.



.