I create a column of formulas, 9600 cells long, in which some result in a
text entry. I then copy this column and Paste Special / Value, then sort,
descending, moving the cells with data to the top. I then manually select
only the cells with data and copy them elsewhere.

I have been able to create a macro that does all the above, except select
only the cells with data. How does one select the part of a column that has
data?

Once I learn this trick, I can do a lot of automating of the operation of
the spreadsheet.
I am using Excel 2000 in Windows 2000

Any assistance would be appreciated,

Thanks, in advance,

Craig

When you say data -do you mean numeric constants or both numeric and text?
Maybe you could use the SpecialCells method. As an example this line of code
would copy numeric constants from Column A of the activesheet to Sheet2.

Sub test()
Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
End Sub

Remove the second argument (xlnumbers) if you want both numeric and text
constants.

"C Brandt" wrote:

I create a column of formulas, 9600 cells long, in which some result in a
text entry. I then copy this column and Paste Special / Value, then sort,
descending, moving the cells with data to the top. I then manually select
only the cells with data and copy them elsewhere.

I have been able to create a macro that does all the above, except select
only the cells with data. How does one select the part of a column that has
data?

Once I learn this trick, I can do a lot of automating of the operation of
the spreadsheet.
I am using Excel 2000 in Windows 2000

Any assistance would be appreciated,

Thanks, in advance,

Craig

why not try Goto\Special\Constants\. You may be able to skip a whole
lot of the other steps. First select the entire range, hit F5 and then
select Special\constants\text. You can then delete all select cells,
leaving just the numbers.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561807

I would like to thank the group for their responses, expecially JMB who gave
me the solution that was needed to solve that particular problem and Gord
for showing me the error in my application.
Where can I find a write-up on the code that was used?

Thanks again,
Craig


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Craig

Should read like this

Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")

The _ is a line continuation mark and is used to place one long line on
two
lines.

Note there is a space before the _


Gord Dibben MS Excel MVP

On Sun, 16 Jul 2006 17:24:54 GMT, "C Brandt"
wrote:

I am clearly way over my head with this, but that is my usual mode of
operation. Ask the experts, pick up a few key ideas, then spend some time
with documentation to figure out what it is they said.
I tried your code as presented and it will not run. It gives me a compile
error [Invalid Character] and points to the underline.

Columns(1).SpecialCells(xlCellTypeConstants,_xlNu mbers).Copy
Sheet2.Range("A1")

I looked in the documentation that I have on-hand and it is of no help.
Is
there a site on-line that may be of help.

Thanks Again,

Craig



"JMB" wrote in message
...
When you say data -do you mean numeric constants or both numeric and
text?
Maybe you could use the SpecialCells method. As an example this line
of
code
would copy numeric constants from Column A of the activesheet to
Sheet2.

Sub test()
Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
End Sub

Remove the second argument (xlnumbers) if you want both numeric and
text
constants.

"C Brandt" wrote:

I create a column of formulas, 9600 cells long, in which some result
in
a
text entry. I then copy this column and Paste Special / Value, then
sort,
descending, moving the cells with data to the top. I then manually
select
only the cells with data and copy them elsewhere.

I have been able to create a macro that does all the above, except
select
only the cells with data. How does one select the part of a column
that
has
data?

Once I learn this trick, I can do a lot of automating of the
operation
of
the spreadsheet.
I am using Excel 2000 in Windows 2000

Any assistance would be appreciated,

Thanks, in advance,

Craig

VBA help is probably the best place to look (look for the Copy method and
SpecialCells).

The basic format is
Source.Copy Destination
where Source and Destination are range objects. For Source, the range is
first restricted to Column A (by specifying Columns(1)). Within this column
you only want particular cells (I guessed numeric constants -see help for
other options for SpecialCells). So SpecialCells returns a range of numeric
constants in Column A. This is copied to the upper left corner of the
destination, cell A1 of Sheet2.

One note I should add. Worksheets actually have two names. The name you
see on the tab and the code name. I habitually use the Code Name for a
worksheet because the tab name can change often (requiring you to edit your
VBA code -the code name can also be changed, but it requires more intent).
The code name and tab name can easily be different. The difference in syntax
(say sheet 2's tab name was changed to test) is:

Sheets("Test").Range("A1")
Sheet2.Range("A1")

If you were to hold down the control key and use the mouse to select the
numeric constants in Column A, then hit copy, go to another sheet (cell A1)
and hit paste -you'll get the same results.

And, if it is possible the source worksheet may not be the activesheet when
the copying is performed, you can qualify the source range further by
preceding it with the worksheet (again, I'm using code names)

Sheet1.Columns(1).SpecialCells(xlCellTypeConstants , _
xlNumbers).Copy Sheet2.Range("A1")

Thanks for the feedback. I'm glad the suggestion is in the right direction.

"C Brandt" wrote:

I would like to thank the group for their responses, expecially JMB who gave
me the solution that was needed to solve that particular problem and Gord
for showing me the error in my application.
Where can I find a write-up on the code that was used?

Thanks again,
Craig


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Craig

Should read like this

Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")

The _ is a line continuation mark and is used to place one long line on
two
lines.

Note there is a space before the _


Gord Dibben MS Excel MVP

On Sun, 16 Jul 2006 17:24:54 GMT, "C Brandt"
wrote:

I am clearly way over my head with this, but that is my usual mode of
operation. Ask the experts, pick up a few key ideas, then spend some time
with documentation to figure out what it is they said.
I tried your code as presented and it will not run. It gives me a compile
error [Invalid Character] and points to the underline.

Columns(1).SpecialCells(xlCellTypeConstants,_xlNu mbers).Copy
Sheet2.Range("A1")

I looked in the documentation that I have on-hand and it is of no help.
Is
there a site on-line that may be of help.

Thanks Again,

Craig



"JMB" wrote in message
...
When you say data -do you mean numeric constants or both numeric and
text?
Maybe you could use the SpecialCells method. As an example this line
of
code
would copy numeric constants from Column A of the activesheet to
Sheet2.

Sub test()
Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
End Sub

Remove the second argument (xlnumbers) if you want both numeric and
text
constants.

"C Brandt" wrote:

I create a column of formulas, 9600 cells long, in which some result
in
a
text entry. I then copy this column and Paste Special / Value, then
sort,
descending, moving the cells with data to the top. I then manually
select
only the cells with data and copy them elsewhere.

I have been able to create a macro that does all the above, except
select
only the cells with data. How does one select the part of a column
that
has
data?

Once I learn this trick, I can do a lot of automating of the
operation
of
the spreadsheet.
I am using Excel 2000 in Windows 2000

Any assistance would be appreciated,

Thanks, in advance,

Craig