| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
Check COlumn - Excel VBA
See response in public.excel
-- HTH RP (remove nothere from the email address if mailing direct) "magix" wrote in message ... 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. |
|
#3
|
|||
|
|||
|
Check COlumn - Excel VBA
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. |
|
#4
|
|||
|
|||
|
Check COlumn - Excel VBA
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 |
|
#5
|
|||
|
|||
|
Check COlumn - Excel VBA
"Duke Carey" wrote in message ... 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. |
|
#6
|
|||
|
|||
|
Check COlumn - Excel VBA
"Dave Peterson" wrote in message ... 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 |
|
#7
|
|||
|
|||
|
Check COlumn - Excel VBA
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" wrote in message ... "Dave Peterson" wrote in message ... 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 |
|
#8
|
|||
|
|||
|
Check COlumn - Excel VBA
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.co unt = 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" wrote in message ... 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 |
|
#9
|
|||
|
|||
|
Check COlumn - Excel VBA
"Dave Peterson" wrote in message ... 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.co unt = 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" wrote in message ... 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 |
|
#10
|
|||
|
|||
|
Check COlumn - Excel VBA
Select A1:A5 and A10:A15 and see if selection.areas.count does what you want.
magix wrote: "Dave Peterson" wrote in message ... 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.co unt = 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" wrote in message ... 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 |
|
#11
|
|||
|
|||
|
Check COlumn - Excel VBA
"Dave Peterson" wrote in message ... Select A1:A5 and A10:A15 and see if selection.areas.count does what you want. magix wrote: "Dave Peterson" wrote in message ... 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.co unt = 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" wrote in message ... 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. |
|
#12
|
|||
|
|||
|
Check COlumn - Excel VBA
I think I'd use something like:
if activecell.column = 1 _ and intersect(selection.entirecolumn,rows(1)).cells.co unt = 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Categorizing an excel column | Excel Discussion (Misc queries) | |||
| match and count words | Excel Worksheet Functions | |||
| Check boxes in a column | Excel Discussion (Misc queries) | |||
| Column A is Town, Column B is names. How can Excel add & tell how. | Charts and Charting in Excel | |||
| How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
