Return value of Selection.Cells.Count

[Skin Paul]

Hi all, using xl 2004 version 11.3.5
Have spent many hours trying to work this out but to no avail.
Question is: How do I get a return value of cell count for for a selection
that has no known parameters? If I know either the number of columns
or rows I can use the below macro to determine the last cell and make it
active. In this macro I know that it has 15 columns. Please excuse poor
Procedure. I would like to activate the last cell in a random selection
After copying and pasting values only. As this would be part of a larger
Procedure I can't just simply select the cell.
Thanks Paul.

Sub Inplace()

Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Roc equals Row counter
'Ae equals cell count per row
Roc = 0
For Ae = 16 To 2000 Step 15
If Selection.Cells.Count < Ae Then
ActiveCell.Offset(Roc, 14).Range("A1").Select
Exit Sub
End If
Roc = Roc + 1
Next Ae
End Sub

 

[Skin Paul]

Hi Cancel post. I just worked it out " MsgBox
ActiveWindow.RangeSelection.Count"!!!

 

[JE McGimpsey]

Hi all, using xl 2004 version 11.3.5
Have spent many hours trying to work this out but to no avail.
Question is: How do I get a return value of cell count for for a selection
that has no known parameters? If I know either the number of columns
or rows I can use the below macro to determine the last cell and make it
active. In this macro I know that it has 15 columns. Please excuse poor
Procedure. I would like to activate the last cell in a random selection
After copying and pasting values only. As this would be part of a larger
Procedure I can't just simply select the cell.

I'm not really sure what you're trying to do, here, but to activate the
last cell in a random selection (at least, one that has only one
continuous area) you can use:

With Selection
.Cells(.Cells.Count).Activate
End With

You could also use

Selection.SpecialCells(xlCellTypeLastCell).Activate

though if only one cell is selected, SpecialCells will return the last
used cell on the entire worksheet.

1) Note that, unless your code operates on a range that depends on the
user's selection at run-time, using Selections is generally unnecessary
- it tends to produce slower code that is harder to maintain than using
range objects directly.


2) A simpler way to convert the selection's formulae to values would be:

With Selection
.Value = .Value
End With

which you could combine with the above:

With Selection
.Value = .Value
.Cells(.Cells.Count).Activate
End With

 

[JE McGimpsey]

Hi Cancel post. I just worked it out " MsgBox
ActiveWindow.RangeSelection.Count"!!!

You could also use

MsgBox Selection.Cells.Count

 

[Skin Paul]

Thanks J.E. Still have not worked out how to utilize the value. Can I store
it by defining a name for it? (in the procedure) How do I access the value
in a procedure?
Thanks Paul

 

[Skin Paul]

Thanks again J.E. I missed your 5.18 post. All sorted now.
The last snippet:
With Selection
.Value = .Value
.Cells(.Cells.Count).Activate
End With
Is brilliant.
Don't know what I would do without all gurus on this forum.
Paul