If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Selecting all the active cells in a named column.
ps. If you are typing in the column address instead of just pointing at the
column, then make sure you enter something like: G:G (If you're using A1 reference style) or C7 (for column 7 (column G)) if you're using R1C1 reference style) Colin Hayes wrote: In article , Dave Peterson writes Do you use merged cells? If yes, then don't select a merged cell/range when prompted. If you can't do that, you're going to have to share how the program should know what cell in the merge area should define that column. If you don't use merged cells, then I think something else went bad. Did you change the code and not share your changes? You should be using your mouse to select a cell? Are you selecting more than one cell? Hi Dave Thanks for getting back. No , no merged cells. Only one cell is selected by mouse, be it randomly or in the column I'm going to choose. When I choose the target column via the macro popup , it selects a different one (I enter 'G' and it selects column N) or crashes out with an error. Very mysterious. I've not made any changes to the code and have implemented it entirely as shown. It just needs to select all cells with content in the column specified in the popup. Best Wishes Colin Hayes wrote: Hi Dave OK Thanks for this. It does the job. Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Could you helps with this , please? Also , it would be helpful if it could highlight starting with cell 2 in the chosen column. Thanks again. In article , Dave Peterson writes If you want a macro to ask the user... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myCell = Nothing On Error Resume Next Set myCell = Application.InputBox(Prompt:="Please select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myCell Is Nothing Then Beep 'user hit cancel Exit Sub End If With myCell.Parent Set myRng = .Range(.Cells(1, myCell.Column), _ .Cells(.Rows.Count, myCell.Column).End(xlUp)) End With Application.Goto myRng End Sub Colin Hayes wrote: Hi I have a small problem. I need to have a macro which can fulfil the following , if possible. I need to select all the cells in a named column. Not the whole column , but just those cells with content. Perhaps the macro could request the column to act on , and then the macro would select from cell 1 down to the last cell with content. Can someone help with this? Grateful for any advice. Best Wishes -- Dave Peterson |
#12
|
|||
|
|||
Selecting all the active cells in a named column.
Hi Dave This is what I've been doing : 1. After running the macro I'm only typing into the popup. It asks for the column letter. When I type J and click OK , the macro selects column D. When I type F into the popup , it says 'The formula you typed contains errors....' 2. I'm not touching the mouse at all , except to click OK on the popup. Am I implementing this in the wrong way ...? I realise now that you're expecting me to select the column I want *with the mouse* , whereas I understood the flashing cursor in the popup saying 'Chose a column' to be asking me to enter the letter of the column I want. Do I have it right now? ^_^ Best Wishes In article , Dave Peterson writes I don't understand what you mean when you write: Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. How are you specifying column J? This: Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Makes me believe that you're not using the mouse to point and click on the cell. If you are typing into that application.inputbox, what are you typing? Colin Hayes wrote: In article , Dave Peterson writes Do you use merged cells? If yes, then don't select a merged cell/range when prompted. If you can't do that, you're going to have to share how the program should know what cell in the merge area should define that column. If you don't use merged cells, then I think something else went bad. Did you change the code and not share your changes? You should be using your mouse to select a cell? Are you selecting more than one cell? Hi Dave Thanks for getting back. No , no merged cells. Only one cell is selected by mouse, be it randomly or in the column I'm going to choose. When I choose the target column via the macro popup , it selects a different one (I enter 'G' and it selects column N) or crashes out with an error. Very mysterious. I've not made any changes to the code and have implemented it entirely as shown. It just needs to select all cells with content in the column specified in the popup. Best Wishes Colin Hayes wrote: Hi Dave OK Thanks for this. It does the job. Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Could you helps with this , please? Also , it would be helpful if it could highlight starting with cell 2 in the chosen column. Thanks again. In article , Dave Peterson writes If you want a macro to ask the user... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myCell = Nothing On Error Resume Next Set myCell = Application.InputBox(Prompt:="Please select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myCell Is Nothing Then Beep 'user hit cancel Exit Sub End If With myCell.Parent Set myRng = .Range(.Cells(1, myCell.Column), _ .Cells(.Rows.Count, myCell.Column).End(xlUp)) End With Application.Goto myRng End Sub Colin Hayes wrote: Hi I have a small problem. I need to have a macro which can fulfil the following , if possible. I need to select all the cells in a named column. Not the whole column , but just those cells with content. Perhaps the macro could request the column to act on , and then the macro would select from cell 1 down to the last cell with content. Can someone help with this? Grateful for any advice. Best Wishes |
#13
|
|||
|
|||
Selecting all the active cells in a named column.
My guess is that you have a range named J.
So even though you think you're typing a column letter, you're not. You're actually typing that name of the cell (or range of cells). And the code uses that named range to determine the column. You can test this by: Edit|Goto (or ctrl-g or F5) type: J and hit enter. So you can do a couple of things--one is use the mouse to select a range. Or enter a real address for the column or cell you want want. If you want to use column J, then type: J:J Or if you just want to specify a single cell, then type: J1 (or J17 or J99) Colin Hayes wrote: Hi Dave This is what I've been doing : 1. After running the macro I'm only typing into the popup. It asks for the column letter. When I type J and click OK , the macro selects column D. When I type F into the popup , it says 'The formula you typed contains errors....' 2. I'm not touching the mouse at all , except to click OK on the popup. Am I implementing this in the wrong way ...? I realise now that you're expecting me to select the column I want *with the mouse* , whereas I understood the flashing cursor in the popup saying 'Chose a column' to be asking me to enter the letter of the column I want. Do I have it right now? ^_^ Best Wishes In article , Dave Peterson writes I don't understand what you mean when you write: Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. How are you specifying column J? This: Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Makes me believe that you're not using the mouse to point and click on the cell. If you are typing into that application.inputbox, what are you typing? Colin Hayes wrote: In article , Dave Peterson writes Do you use merged cells? If yes, then don't select a merged cell/range when prompted. If you can't do that, you're going to have to share how the program should know what cell in the merge area should define that column. If you don't use merged cells, then I think something else went bad. Did you change the code and not share your changes? You should be using your mouse to select a cell? Are you selecting more than one cell? Hi Dave Thanks for getting back. No , no merged cells. Only one cell is selected by mouse, be it randomly or in the column I'm going to choose. When I choose the target column via the macro popup , it selects a different one (I enter 'G' and it selects column N) or crashes out with an error. Very mysterious. I've not made any changes to the code and have implemented it entirely as shown. It just needs to select all cells with content in the column specified in the popup. Best Wishes Colin Hayes wrote: Hi Dave OK Thanks for this. It does the job. Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Could you helps with this , please? Also , it would be helpful if it could highlight starting with cell 2 in the chosen column. Thanks again. In article , Dave Peterson writes If you want a macro to ask the user... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myCell = Nothing On Error Resume Next Set myCell = Application.InputBox(Prompt:="Please select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myCell Is Nothing Then Beep 'user hit cancel Exit Sub End If With myCell.Parent Set myRng = .Range(.Cells(1, myCell.Column), _ .Cells(.Rows.Count, myCell.Column).End(xlUp)) End With Application.Goto myRng End Sub Colin Hayes wrote: Hi I have a small problem. I need to have a macro which can fulfil the following , if possible. I need to select all the cells in a named column. Not the whole column , but just those cells with content. Perhaps the macro could request the column to act on , and then the macro would select from cell 1 down to the last cell with content. Can someone help with this? Grateful for any advice. Best Wishes -- Dave Peterson |
#14
|
|||
|
|||
Selecting all the active cells in a named column.
This:
So even though you think you're typing a column letter, you're not. Maybe be better as: So even though you think you're typing a column's ADDRESS, you're not. Dave Peterson wrote: My guess is that you have a range named J. So even though you think you're typing a column letter, you're not. You're actually typing that name of the cell (or range of cells). And the code uses that named range to determine the column. You can test this by: Edit|Goto (or ctrl-g or F5) type: J and hit enter. So you can do a couple of things--one is use the mouse to select a range. Or enter a real address for the column or cell you want want. If you want to use column J, then type: J:J Or if you just want to specify a single cell, then type: J1 (or J17 or J99) Colin Hayes wrote: Hi Dave This is what I've been doing : 1. After running the macro I'm only typing into the popup. It asks for the column letter. When I type J and click OK , the macro selects column D. When I type F into the popup , it says 'The formula you typed contains errors....' 2. I'm not touching the mouse at all , except to click OK on the popup. Am I implementing this in the wrong way ...? I realise now that you're expecting me to select the column I want *with the mouse* , whereas I understood the flashing cursor in the popup saying 'Chose a column' to be asking me to enter the letter of the column I want. Do I have it right now? ^_^ Best Wishes In article , Dave Peterson writes I don't understand what you mean when you write: Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. How are you specifying column J? This: Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Makes me believe that you're not using the mouse to point and click on the cell. If you are typing into that application.inputbox, what are you typing? Colin Hayes wrote: In article , Dave Peterson writes Do you use merged cells? If yes, then don't select a merged cell/range when prompted. If you can't do that, you're going to have to share how the program should know what cell in the merge area should define that column. If you don't use merged cells, then I think something else went bad. Did you change the code and not share your changes? You should be using your mouse to select a cell? Are you selecting more than one cell? Hi Dave Thanks for getting back. No , no merged cells. Only one cell is selected by mouse, be it randomly or in the column I'm going to choose. When I choose the target column via the macro popup , it selects a different one (I enter 'G' and it selects column N) or crashes out with an error. Very mysterious. I've not made any changes to the code and have implemented it entirely as shown. It just needs to select all cells with content in the column specified in the popup. Best Wishes Colin Hayes wrote: Hi Dave OK Thanks for this. It does the job. Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Could you helps with this , please? Also , it would be helpful if it could highlight starting with cell 2 in the chosen column. Thanks again. In article , Dave Peterson writes If you want a macro to ask the user... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myCell = Nothing On Error Resume Next Set myCell = Application.InputBox(Prompt:="Please select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myCell Is Nothing Then Beep 'user hit cancel Exit Sub End If With myCell.Parent Set myRng = .Range(.Cells(1, myCell.Column), _ .Cells(.Rows.Count, myCell.Column).End(xlUp)) End With Application.Goto myRng End Sub Colin Hayes wrote: Hi I have a small problem. I need to have a macro which can fulfil the following , if possible. I need to select all the cells in a named column. Not the whole column , but just those cells with content. Perhaps the macro could request the column to act on , and then the macro would select from cell 1 down to the last cell with content. Can someone help with this? Grateful for any advice. Best Wishes -- Dave Peterson -- Dave Peterson |
#15
|
|||
|
|||
Selecting all the active cells in a named column.
In article , Dave Peterson
writes This: So even though you think you're typing a column letter, you're not. Maybe be better as: So even though you think you're typing a column's ADDRESS, you're not. Hi Dave Yes , I see the distinction. Now I can see how you intended it to be used , I find it's working perfectly. Thanks for your help and your expertise. Best Wishes Dave Peterson wrote: My guess is that you have a range named J. So even though you think you're typing a column letter, you're not. You're actually typing that name of the cell (or range of cells). And the code uses that named range to determine the column. You can test this by: Edit|Goto (or ctrl-g or F5) type: J and hit enter. So you can do a couple of things--one is use the mouse to select a range. Or enter a real address for the column or cell you want want. If you want to use column J, then type: J:J Or if you just want to specify a single cell, then type: J1 (or J17 or J99) Colin Hayes wrote: Hi Dave This is what I've been doing : 1. After running the macro I'm only typing into the popup. It asks for the column letter. When I type J and click OK , the macro selects column D. When I type F into the popup , it says 'The formula you typed contains errors....' 2. I'm not touching the mouse at all , except to click OK on the popup. Am I implementing this in the wrong way ...? I realise now that you're expecting me to select the column I want *with the mouse* , whereas I understood the flashing cursor in the popup saying 'Chose a column' to be asking me to enter the letter of the column I want. Do I have it right now? ^_^ Best Wishes In article , Dave Peterson writes I don't understand what you mean when you write: Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. How are you specifying column J? This: Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Makes me believe that you're not using the mouse to point and click on the cell. If you are typing into that application.inputbox, what are you typing? Colin Hayes wrote: In article , Dave Peterson writes Do you use merged cells? If yes, then don't select a merged cell/range when prompted. If you can't do that, you're going to have to share how the program should know what cell in the merge area should define that column. If you don't use merged cells, then I think something else went bad. Did you change the code and not share your changes? You should be using your mouse to select a cell? Are you selecting more than one cell? Hi Dave Thanks for getting back. No , no merged cells. Only one cell is selected by mouse, be it randomly or in the column I'm going to choose. When I choose the target column via the macro popup , it selects a different one (I enter 'G' and it selects column N) or crashes out with an error. Very mysterious. I've not made any changes to the code and have implemented it entirely as shown. It just needs to select all cells with content in the column specified in the popup. Best Wishes Colin Hayes wrote: Hi Dave OK Thanks for this. It does the job. Unfortunately though , when I specify column J via the popup , it selects D. When I specify I , it selects C. Also , when I specify other columns , I get 'The formula you typed contains errors...' issues. Could you helps with this , please? Also , it would be helpful if it could highlight starting with cell 2 in the chosen column. Thanks again. In article , Dave Peterson writes If you want a macro to ask the user... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myCell = Nothing On Error Resume Next Set myCell = Application.InputBox(Prompt:="Please select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myCell Is Nothing Then Beep 'user hit cancel Exit Sub End If With myCell.Parent Set myRng = .Range(.Cells(1, myCell.Column), _ .Cells(.Rows.Count, myCell.Column).End(xlUp)) End With Application.Goto myRng End Sub Colin Hayes wrote: Hi I have a small problem. I need to have a macro which can fulfil the following , if possible. I need to select all the cells in a named column. Not the whole column , but just those cells with content. Perhaps the macro could request the column to act on , and then the macro would select from cell 1 down to the last cell with content. Can someone help with this? Grateful for any advice. Best Wishes -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|