Check COlumn - Excel VBA

[magix]

Hi,

In excel VBA code, I have:

If ActiveCell.Column =1 Then
Msg "It is Column A Only"
Else
Msg "It is NOT Column A"
End If


But How can I check if the selection is actually More than Column A.
Example: When user select cell in column A (A11) and cell in column B (B11),
if use above code, it will still prompt "It is Column A Only". I want to
avoid that because B11 is included.

I think I should have something like:

If ActiveCell.Column =1 Then
' Check if there is other column included
If there is other column than column A included
Msg "Warning, it's NOT only Column A"
Else
Msg "It is Column A Only"
End If
Else
Msg "It is NOT Column A"
End If

Thanks in advance.

Regards.

[Bob Phillips]

See response in public.excel

--

HTH

RP
(remove nothere from the email address if mailing direct)


"magix" <magix@asia.com> wrote in message
news:436b423b$3_1@news.tm.net.my...
> Hi,
>
> In excel VBA code, I have:
>
> If ActiveCell.Column =1 Then
> Msg "It is Column A Only"
> Else
> Msg "It is NOT Column A"
> End If
>
>
> But How can I check if the selection is actually More than Column A.
> Example: When user select cell in column A (A11) and cell in column B
(B11),
> if use above code, it will still prompt "It is Column A Only". I want to
> avoid that because B11 is included.
>
> I think I should have something like:
>
> If ActiveCell.Column =1 Then
> ' Check if there is other column included
> If there is other column than column A included
> Msg "Warning, it's NOT only Column A"
> Else
> Msg "It is Column A Only"
> End If
> Else
> Msg "It is NOT Column A"
> End If
>
> Thanks in advance.
>
> Regards.
>
>

[Duke Carey]

if ActiveCell.Column =1 and selection.columns.count=1 Then

etc.


"magix" wrote:

> Hi,
>
> In excel VBA code, I have:
>
> If ActiveCell.Column =1 Then
> Msg "It is Column A Only"
> Else
> Msg "It is NOT Column A"
> End If
>
>
> But How can I check if the selection is actually More than Column A.
> Example: When user select cell in column A (A11) and cell in column B (B11),
> if use above code, it will still prompt "It is Column A Only". I want to
> avoid that because B11 is included.
>
> I think I should have something like:
>
> If ActiveCell.Column =1 Then
> ' Check if there is other column included
> If there is other column than column A included
> Msg "Warning, it's NOT only Column A"
> Else
> Msg "It is Column A Only"
> End If
> Else
> Msg "It is NOT Column A"
> End If
>
> Thanks in advance.
>
> Regards.
>
>
>

[Dave Peterson]

Selection.columns.count will work if the selection is one contiguous range. But
will fail if the selection is multiple discontiguous areas.

Range("a1:a10,c1:c10,e1:e10").Select
MsgBox Selection.Columns.Count

One way around it:

Range("a1:a10,c1:c10,e1:e10").Select
MsgBox Intersect(Selection.EntireColumn, Rows(1)).Cells.Count

Duke Carey wrote:
>
> if ActiveCell.Column =1 and selection.columns.count=1 Then
>
> etc.
>
> "magix" wrote:
>
> > Hi,
> >
> > In excel VBA code, I have:
> >
> > If ActiveCell.Column =1 Then
> > Msg "It is Column A Only"
> > Else
> > Msg "It is NOT Column A"
> > End If
> >
> >
> > But How can I check if the selection is actually More than Column A.
> > Example: When user select cell in column A (A11) and cell in column B (B11),
> > if use above code, it will still prompt "It is Column A Only". I want to
> > avoid that because B11 is included.
> >
> > I think I should have something like:
> >
> > If ActiveCell.Column =1 Then
> > ' Check if there is other column included
> > If there is other column than column A included
> > Msg "Warning, it's NOT only Column A"
> > Else
> > Msg "It is Column A Only"
> > End If
> > Else
> > Msg "It is NOT Column A"
> > End If
> >
> > Thanks in advance.
> >
> > Regards.
> >
> >
> >

--

Dave Peterson

[magix]

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:436B6B40.3C7E89AE@verizonXSPAM.net...
> Selection.columns.count will work if the selection is one contiguous
range. But
> will fail if the selection is multiple discontiguous areas.
>
> Range("a1:a10,c1:c10,e1:e10").Select
> MsgBox Selection.Columns.Count
>
> One way around it:
>
> Range("a1:a10,c1:c10,e1:e10").Select
> MsgBox Intersect(Selection.EntireColumn, Rows(1)).Cells.Count
>
> Duke Carey wrote:
> >
> > if ActiveCell.Column =1 and selection.columns.count=1 Then
> >
> > etc.
> >
> > "magix" wrote:
> >
> > > Hi,
> > >
> > > In excel VBA code, I have:
> > >
> > > If ActiveCell.Column =1 Then
> > > Msg "It is Column A Only"
> > > Else
> > > Msg "It is NOT Column A"
> > > End If
> > >
> > >
> > > But How can I check if the selection is actually More than Column A.
> > > Example: When user select cell in column A (A11) and cell in column B
(B11),
> > > if use above code, it will still prompt "It is Column A Only". I want
to
> > > avoid that because B11 is included.
> > >
> > > I think I should have something like:
> > >
> > > If ActiveCell.Column =1 Then
> > > ' Check if there is other column included
> > > If there is other column than column A included
> > > Msg "Warning, it's NOT only Column A"
> > > Else
> > > Msg "It is Column A Only"
> > > End If
> > > Else
> > > Msg "It is NOT Column A"
> > > End If
> > >
> > > Thanks in advance.
> > >
> > > Regards.
> > >
> > >
> > >
>
> --
>
> Dave Peterson

Hi Dave,

You have your point here. If that the case, how can I improve the below
statement ?

" if ActiveCell.Column =1 and selection.columns.count=1 Then"

Regards,
Magix

[Bob Phillips]

If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And _
Selection.Areas.Count <> 1 Then


--

HTH

RP
(remove nothere from the email address if mailing direct)


"magix" <magix@asia.com> wrote in message news:436b8cda_1@news.tm.net.my...
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:436B6B40.3C7E89AE@verizonXSPAM.net...
> > Selection.columns.count will work if the selection is one contiguous
> range. But
> > will fail if the selection is multiple discontiguous areas.
> >
> > Range("a1:a10,c1:c10,e1:e10").Select
> > MsgBox Selection.Columns.Count
> >
> > One way around it:
> >
> > Range("a1:a10,c1:c10,e1:e10").Select
> > MsgBox Intersect(Selection.EntireColumn, Rows(1)).Cells.Count
> >
> > Duke Carey wrote:
> > >
> > > if ActiveCell.Column =1 and selection.columns.count=1 Then
> > >
> > > etc.
> > >
> > > "magix" wrote:
> > >
> > > > Hi,
> > > >
> > > > In excel VBA code, I have:
> > > >
> > > > If ActiveCell.Column =1 Then
> > > > Msg "It is Column A Only"
> > > > Else
> > > > Msg "It is NOT Column A"
> > > > End If
> > > >
> > > >
> > > > But How can I check if the selection is actually More than Column A.
> > > > Example: When user select cell in column A (A11) and cell in column
B
> (B11),
> > > > if use above code, it will still prompt "It is Column A Only". I
want
> to
> > > > avoid that because B11 is included.
> > > >
> > > > I think I should have something like:
> > > >
> > > > If ActiveCell.Column =1 Then
> > > > ' Check if there is other column included
> > > > If there is other column than column A included
> > > > Msg "Warning, it's NOT only Column A"
> > > > Else
> > > > Msg "It is Column A Only"
> > > > End If
> > > > Else
> > > > Msg "It is NOT Column A"
> > > > End If
> > > >
> > > > Thanks in advance.
> > > >
> > > > Regards.
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
>
> Hi Dave,
>
> You have your point here. If that the case, how can I improve the below
> statement ?
>
> " if ActiveCell.Column =1 and selection.columns.count=1 Then"
>
> Regards,
> Magix
>
>

[Dave Peterson]

Maybe instead of:

if ActiveCell.Column =1 and selection.columns.count=1 Then

This...

if activecell.column = 1 _
and intersect(selection.entirecolumn,rows(1)).cells.count = 1 then

This would allow you to select A1:A10 and A21:A30, too.

======
I'm not sure what you want, but maybe you could just use the stuff in column A
after you extend the selection to be the complete row. Selecting B23:G39 would
result in a range of A23:A39.

dim myRng as range
set myrng = intersect(selection.entirerow,range("a:a"))

But that kind of thing depends on what you're really doing.

magix wrote:
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:436B6B40.3C7E89AE@verizonXSPAM.net...
> > Selection.columns.count will work if the selection is one contiguous
> range. But
> > will fail if the selection is multiple discontiguous areas.
> >
> > Range("a1:a10,c1:c10,e1:e10").Select
> > MsgBox Selection.Columns.Count
> >
> > One way around it:
> >
> > Range("a1:a10,c1:c10,e1:e10").Select
> > MsgBox Intersect(Selection.EntireColumn, Rows(1)).Cells.Count
> >
> > Duke Carey wrote:
> > >
> > > if ActiveCell.Column =1 and selection.columns.count=1 Then
> > >
> > > etc.
> > >
> > > "magix" wrote:
> > >
> > > > Hi,
> > > >
>
> > > In excel VBA code, I have:
> > > >
> > > > If ActiveCell.Column =1 Then
> > > > Msg "It is Column A Only"
> > > > Else
> > > > Msg "It is NOT Column A"
> > > > End If
> > > >
> > > >
> > > > But How can I check if the selection is actually More than Column A.
> > > > Example: When user select cell in column A (A11) and cell in column B
> (B11),
> > > > if use above code, it will still prompt "It is Column A Only". I want
> to
> > > > avoid that because B11 is included.
> > > >
> > > > I think I should have something like:
> > > >
> > > > If ActiveCell.Column =1 Then
> > > > ' Check if there is other column included
> > > > If there is other column than column A included
> > > > Msg "Warning, it's NOT only Column A"
> > > > Else
> > > > Msg "It is Column A Only"
> > > > End If
> > > > Else
> > > > Msg "It is NOT Column A"
> > > > End If
> > > >
> > > > Thanks in advance.
> > > >
> > > > Regards.
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
>
> Hi Dave,
>
> You have your point here. If that the case, how can I improve the below
> statement ?
>
> " if ActiveCell.Column =1 and selection.columns.count=1 Then"
>
> Regards,
> Magix

--

Dave Peterson

[magix]

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:436B96C4.F9D9752@verizonXSPAM.net...
> Maybe instead of:
>
> if ActiveCell.Column =1 and selection.columns.count=1 Then
>
> This...
>
> if activecell.column = 1 _
> and intersect(selection.entirecolumn,rows(1)).cells.count = 1 then
>
> This would allow you to select A1:A10 and A21:A30, too.
>
> ======
> I'm not sure what you want, but maybe you could just use the stuff in
column A
> after you extend the selection to be the complete row. Selecting B23:G39
would
> result in a range of A23:A39.
>
> dim myRng as range
> set myrng = intersect(selection.entirerow,range("a:a"))
>
> But that kind of thing depends on what you're really doing.
>
> magix wrote:
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:436B6B40.3C7E89AE@verizonXSPAM.net...
> > > Selection.columns.count will work if the selection is one contiguous
> > range. But
> > > will fail if the selection is multiple discontiguous areas.
> > >
> > > Range("a1:a10,c1:c10,e1:e10").Select
> > > MsgBox Selection.Columns.Count
> > >
> > > One way around it:
> > >
> > > Range("a1:a10,c1:c10,e1:e10").Select
> > > MsgBox Intersect(Selection.EntireColumn, Rows(1)).Cells.Count
> > >
> > > Duke Carey wrote:
> > > >
> > > > if ActiveCell.Column =1 and selection.columns.count=1 Then
> > > >
> > > > etc.
> > > >
> > > > "magix" wrote:
> > > >
> > > > > Hi,
> > > > >
> >
> > > > In excel VBA code, I have:
> > > > >
> > > > > If ActiveCell.Column =1 Then
> > > > > Msg "It is Column A Only"
> > > > > Else
> > > > > Msg "It is NOT Column A"
> > > > > End If
> > > > >
> > > > >
> > > > > But How can I check if the selection is actually More than Column
A.
> > > > > Example: When user select cell in column A (A11) and cell in
column B
> > (B11),
> > > > > if use above code, it will still prompt "It is Column A Only". I
want
> > to
> > > > > avoid that because B11 is included.
> > > > >
> > > > > I think I should have something like:
> > > > >
> > > > > If ActiveCell.Column =1 Then
> > > > > ' Check if there is other column included
> > > > > If there is other column than column A included
> > > > > Msg "Warning, it's NOT only Column A"
> > > > > Else
> > > > > Msg "It is Column A Only"
> > > > > End If
> > > > > Else
> > > > > Msg "It is NOT Column A"
> > > > > End If
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Regards.
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> >
> > Hi Dave,
> >
> > You have your point here. If that the case, how can I improve the below
> > statement ?
> >
> > " if ActiveCell.Column =1 and selection.columns.count=1 Then"
> >
> > Regards,
> > Magix
>
> --
>
> Dave Peterson

Thanks again. I think it should be "Selection.Areas.count = 1" instead

[magix]

"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:018C31C3-D45C-4E04-83BA-B834EE0B1EF4@microsoft.com...
> if ActiveCell.Column =1 and selection.columns.count=1 Then
>
> etc.
>
>
> "magix" wrote:
>
> > Hi,
> >
> > In excel VBA code, I have:
> >
> > If ActiveCell.Column =1 Then
> > Msg "It is Column A Only"
> > Else
> > Msg "It is NOT Column A"
> > End If
> >
> >
> > But How can I check if the selection is actually More than Column A.
> > Example: When user select cell in column A (A11) and cell in column B
(B11),
> > if use above code, it will still prompt "It is Column A Only". I want to
> > avoid that because B11 is included.
> >
> > I think I should have something like:
> >
> > If ActiveCell.Column =1 Then
> > ' Check if there is other column included
> > If there is other column than column A included
> > Msg "Warning, it's NOT only Column A"
> > Else
> > Msg "It is Column A Only"
> > End If
> > Else
> > Msg "It is NOT Column A"
> > End If
> >
> > Thanks in advance.
> >
> > Regards.
> >
> >
> >


Thanks to all of you.