Re: Vedr: Vedr: [ExcelVBA] Select range in column with non-continuous data

 

Dear All

After further consideration it seems to me that this is a common requirement, but it needs to work for any column or columns.

Accordingly the following code, when assigned to a macro button, will select the entire columns of the selected cells in the same way that CurrentRegion selects an entire rectangle.

Put this code in your Personal.xls :-  

Sub SelectCurrentColumn() 'Derek Turner 2012
    With Selection
        Range(Cells(.CurrentRegion.Row, .Column), _
               Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, _
                .Column + .Columns.Count - 1)).Select
    End With
End Sub

and then assign it to a macro button. 

I have used this three times in anger already. The only thing I can't explain is why I did not think of it years ago.

Regards

Derek Turner
Office Automation Consultant
+++

>________________________________
> From: Derek Turner <g4swy@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Thursday, 28 June 2012, 5:59
>Subject: Re: Vedr: Vedr: [ExcelVBA] Select range in column with non-continuous data
>
>
> 
>Dear Torstein
>
>Your own solution is still too cumbersome. There are too many keystrokes.
>
>What you should do is assign this macro to a button at the top of the Excel window  :-
>
>Sub SelectAllCellsInColumnB()
>   Range(Cells(1, 2), Cells(Range("a1").CurrentRegion.Rows.Count, 2)).Select
>End Sub
>
>I don't know what Excel version you have, so my advice if you do not know about this is to do a Google search on "excel macro button create" and look at the many tutorials.
>
>This is a VBA forum, so expect VBA solutions.
>
>Regards
>
>Derek +++
>
>>________________________________
>> From: Torstein Johnsen <sejohnse@yahoo.no>
>>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>>Sent: Wednesday, 27 June 2012, 21:00
>>Subject: Vedr: Vedr: [ExcelVBA] Select range in column with non-continuous data
>>
>>
>> 
>>Hi Lisa. Paul and Ashok!
>> 
>>First of all, my native language is not english and because of that my questions may be hard for you to understand, and your answers may be difficult for me to understand.
>> 
>>In spite of this I have got a lot of help, specially with VBA-topics!
>> 
>>So, please, let me ask silly questions when I don't understand you!
>> 
>>And now I understood your solutions!
>> 
>>Experimenting with your solution I suddenly came over another one, hope you understand it :)
>> 
>>A1
>>shift-end-down (select all cells in column A)
>>shift-right (extends the range to column A and B)
>>shift-tab
>>right
>> 
>>And your question Paul - why?
>> 
>>Selecting all the cells in column B (down to the last used row) with shift-pgdn is quite cumbersome. With this few keystrokes all cells in column B are selected.
>> 
>>Thanks again
>> 
>>Torstein
>>
>>[Non-text portions of this message have been removed]
>>
>>
>>
>>
>>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (14)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___