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

Try this but why SELECT when you usually don't want to.

Sub selectcellsinnamedrange()
Range("named block").SpecialCells(xlCellTypeConstants).Select
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...


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

Hi Colin

This sheet code will act when you double click any cell in any column, to
select from the first cell down the last cell with any entry.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lr As Long, lc As Long
lc = Target.Column
lr = Cells(Rows.Count, lc).End(xlUp).Row
Range(Cells(1, lc), Cells(lr, lc)).Select
End Sub

To USE
Copy code above
Right click on sheet tabView code
Paste into the white pane that appears
Alt+F11 to return to Excel
--
Regards
Roger Govier

"Colin Hayes" wrote in message
...


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

__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

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

I see from the replies to OP from yourself and Roger as opposed to Don's
reply we have an interpretation problem.

Hope OP posts back<g


Gord

On Wed, 21 Oct 2009 17:24:41 -0500, Dave Peterson
wrote:

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

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

In article , Roger
Govier <roger@technology4unospamdotcodotuk.? writes
Hi Colin

This sheet code will act when you double click any cell in any column, to
select from the first cell down the last cell with any entry.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lr As Long, lc As Long
lc = Target.Column
lr = Cells(Rows.Count, lc).End(xlUp).Row
Range(Cells(1, lc), Cells(lr, lc)).Select
End Sub

To USE
Copy code above
Right click on sheet tabView code
Paste into the white pane that appears
Alt+F11 to return to Excel

HI Roger

Yes, this would do the trick , and is very neat , but I do need it to be
a stand alone macro to fit the circumstance I have. The sheets I'd be
applying it to would change , so a macro would be better.

Thanks again.

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?

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

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

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

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

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

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

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

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