Check COlumn - Excel VBA

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.

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.
>
>

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.
>
>
>

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

"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.

"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

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
>
>

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

"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

Select A1:A5 and A10:A15 and see if selection.areas.count does what you want.

magix wrote:
>
> "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

--

Dave Peterson

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:436C0D06.4F5D4229@verizonXSPAM.net...
> Select A1:A5 and A10:A15 and see if selection.areas.count does what you
want.
>
> magix wrote:
> >
> > "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
>
> --
>
> Dave Peterson


Hi Dave, the code :

"If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
Selection.Areas.Count = 1 Then"

works fine as well as your new code in my other posting. But I got one bug.

Let say my code in the macro button is like this:
If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
Selection.Areas.Count = 1 Then
MsgBox " Test 111"
else
MsgBox "Test 555"

Then in Column A, let say if I select cell A2, then press CTRL, then select
A5, and then press the macro button, it will prompt me Test 555, instead of
Test 111. That's the bug.

Meaning that if select multiple discontiguous areas in Column A only, it
will return false for the IF statement:
"If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
Selection.Areas.Count = 1 "

How can I fix that ?

Thanks in advance.

Regards.

I think I'd use something like:

if activecell.column = 1 _
and intersect(selection.entirecolumn,rows(1)).cells.count = 1 then
MsgBox " Test 111"
else
MsgBox "Test 555"
end if

(But I'm repeating myself <vbg>.)

magix wrote:
>
<<snipped>>
>
> Hi Dave, the code :
>
> "If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
> Selection.Areas.Count = 1 Then"
>
> works fine as well as your new code in my other posting. But I got one bug.
>
> Let say my code in the macro button is like this:
> If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
> Selection.Areas.Count = 1 Then
> MsgBox " Test 111"
> else
> MsgBox "Test 555"
>
> Then in Column A, let say if I select cell A2, then press CTRL, then select
> A5, and then press the macro button, it will prompt me Test 555, instead of
> Test 111. That's the bug.
>
> Meaning that if select multiple discontiguous areas in Column A only, it
> will return false for the IF statement:
> "If ActiveCell.Column = 1 And Selection.Columns.Count = 1 And
> Selection.Areas.Count = 1 "
>
> How can I fix that ?
>
> Thanks in advance.
>
> Regards.

--

Dave Peterson