Hi, I have the following code which will determine if a user has selected a entire column.
Although it worked, but is there a better way to do it? I was thinking if future version of Excel goes beyond 65536 rows, then this code is definitely unusable!!
Re: Determine If Entire Column Is Selected
Hi
I believe that Excel 2006 will have 1m rows, when you implement that change your parameter accordingly.
Or you could find the last row and use the result as the parameter which would accout for any version of Excel
Dim R As Double
R = Selection.End(xlDown).Row
If Selection.Rows.Count = R Then
MsgBox "User has selected an entire column."
End IfRobert
Re: Determine If Entire Column Is Selected
Hi Robert B,
Thanks for your idea. However, I can't seem to get your code to work. I believe the problem lies in:
Let's assume I have some data in column A and it starts at the 2nd row. If I run your code, "R" will give me a value of 2 instead of the maximum rows in a sheet.
Anyway, for anyone using these codes, I think Carlmack's code worked out fine. Nice.
Re: Determine If Entire Column Is Selected
Keeping in mind that any given selection (using CTRL) may or may not be contiguous, or even adjacent it leaves Selection.Rows.Count open to ambiguities.
Perhaps like this would allow for multi range selections:
[VBA]Sub cols()
Dim r As Range, c As Range
For Each c In Selection.Columns
Set r = Intersect(Selection, c)
If r.Address = c.EntireColumn.Address Then
MsgBox "Entire column selected: " & _
Left(c.Cells(1).Address(0, 0), Len(c.Cells(1).Address(0, 0)) - 1)
End If
Next
End Sub[/VBA]
Don’t have an account yet? Register yourself now and be a part of our community!