Selecting all active cells on a spreadsheet

[Lou_Reed]

In the code that was given to me by someone here I take is to select cells in an Excell spreadsheet.I would guess all active cells. It selects cells alright, but only a few. It does not select all active cells. it select just a section of them.

What is wrong here?

Any help appreciated. Thanks in advance.


Respectfully,

Lou Reed

The code is:


Sub selectcells()
Dim Rng As Range


Set Rng = Range(Cells(1, 1), ActiveCell)






'Selecting only hardcoded data
Rng.SpecialCells(xlCellTypeConstants).Select
End Sub

[JoeM]

ActiveCell in Excel is only a single cell. So it is dependent upon whatever cell is the ActiveCell at the time when the code is run.
I would highly recommend not having your VBA code run against something as subjective as that.
If you want to run it against an entire selected range, you would use "Selection", not "ActiveCell" ("Selection" can be multiple cells, "ActiveCell" is just a single cell).

The question is, what do you really want this code to do (specifically, what range do you want it to run against)?

[ranman256]

try:

ActiveSheet.UsedRange.Select

[Lou_Reed]

I want the code to select all cells that have a value in them, preferably a numerical value, but I will take selecting cells that have a text value in them. I do not trust that I (or anyone else) can select all the numerical cells in such a large spreadsheet.

I would rather the software does it. If I can write the correct VBA code then anyone can easily select all the correct cells by pressing a button on the spreadsheet. I believe that is the best way to insure that all cells are selected.

So if it can be done then how do I do it?

Respectfully,

Lou Reed

[JoeM]

I want the code to select all cells that have a value in them

Yes, that is very different than the ActiveCell.

Did you try RanMan's suggestion?

[Lou_Reed]

I did not use Raman's suggestion - yet. I find now with what I have for VBA code that after a couple of presses of the select
fields button thta all fileds with values in them are selected - along with some other fields, but so what that is okay.

Again I have to press the select fields button more than once to get my relevant fields slected, but at least
they are selected.

Where do I put Raman code if i want to use it? What line in there now does it replace?

Does his suggestion allow for only one pressing of the "Select Fields" button.

Any help appreciated. tahnks in advance.

Respectfully,

Lou Reed

[JoeM]

You can combine Ranman's suggestion with your code, to condense it down to a one-line procedure:

Sub selectcells()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Select
End Sub

[Lou_Reed]

Yes, that works very well.

Thanks you very much.

Respectfully,

Lou Reed

[JoeM]

You are welcome.

[Lou_Reed]

i just learned that when I use the code in Post #7 that it selects everything, all cells regardless of whether they are occupied or empty. Thus would be okay except when I go to change selected cells to text it
gets bogged down in the process, the hourglass icon is there, but it never stops like an endless loop. Thus I would like to modify the code from what I am using to something else that can only select the relevant cells, because as I said that it is just to slow when it comes to changing the cells from numbers to text. - I do not know that it will ever stop. It seems to go on continuously.
So a modification is in order. How do I do it?

Any help appreciated. Thanks in advance.


Respectfully,

Lou Reed

[JoeM]

How do you define "relevant cells"?
You need to explain to us the logic of how to determine exactly which cells to include.

[Lou_Reed]

Relevant cells are as few cells as possible, but all cells with numbers in them are selected. It makes no sense to select every cell in the spreadsheet. If I do when it comes time to add an apostrophe
the process as I said gets very bogged down. It may be stuck in a infinite loop or it may be just a long process, either way, I have never waited to see the end of the process so I do not know.

It seems that there must be a better way. That is all that I am asking.

Respectfully,

Lou Reed

[JoeM]

It makes no sense to select every cell in the spreadsheet.

It doesn't. It only selects the UsedRange. It shouldn't select every cell unless you have used every cell.

Relevant cells are as few cells as possible, but all cells with numbers in them are selected.

That's too generic an explanation. We need to more defined rule we can program too.
Are there any certain columns you are concerned with?
What exactly is it that you intend to do with the cells you are selecting?

[Lou_Reed]

The cells that I am concerned with are thee cells with values in them. A blank cells should stay blank. A cells that is occupied should be selected.

That is all. If it selects all cells now, well I told you what would happen.

Respectfully,

Lou Reed

[JoeM]

This code will only select cells that have values in them:

Sub selectcells()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Select
End Sub

If you run that code by itself (and NO other code), does it seem to select the correct cells?
If not, tell me the address of the very first incorrect cell it selects, and tell me what is in that cell.