Hi Torstein
I am glad that you found the routine useful.
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
Here is a simplified version written in a self-documenting style which may explain how it works :-
Sub SelectCurrentColumn() 'Derek Turner 2012
Dim nTopRow As Long, nLeftColumn As Long, nRightColumn As Long, nBottomRow As Long
Dim nRowsToSelect As Long, nColumnsToSelect As Long
With Selection
nTopRow = .CurrentRegion.Row
nLeftColumn = .Column
nRowsToSelect = .CurrentRegion.Rows.Count
nColumnsToSelect = .Columns.Count
End With
nBottomRow = nTopRow + nRowsToSelect - 1
nRightColumn = nLeftColumn + nColumnsToSelect - 1
Range(Cells(nTopRow, nLeftColumn), Cells(nBottomRow, nRightColumn)).Select
End Sub
To understand this you have to know a little about VBA but a bit more about the Excel Object Model .
Let me know if any part needs further explanation.
+++
After further consideration of this code it seems to me that it would be more generally useful and re-usable if it were used by calling a function like this :-
Sub SelectCurrentColumn()
CurrentColumn(Selection).Select
End Sub
Function CurrentColumn(rInput As Range) As Range 'Derek Turner 2012
With rInput
Set CurrentColumn = Range(Cells(.CurrentRegion.Row, .Column), _
Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, _
.Column + .Columns.Count - 1)).Cells
End With
End Function
You can then use the function in your programs like this :-
With CurrentColumn(Range("b1:c1")).Font
.Color = vbRed
.Italic = True
.Bold = False
End With
Regards
Derek Turner +++
Office Automation Consultant
PS if Capital A Circumflexes have spoiled the appearance of this post then please report this bug to Yahoo Groups Central. If enough people do it they might take note.
+++
>________________________________
> From: Torstein Johnsen <sejohnse@yahoo.no>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Wednesday, 4 July 2012, 22:01
>Subject: Vedr: [ExcelVBA] Select range in column with non-continuous data - how does Sub SelectCurrentColumn() work?
>
>
>
>Hi Derek! I have already used your sub many times, and I really like it, works at different situastions. I believe I have some experience in VBA programming, but I'm not able to figure out how your Sub SelectCurrentColumn() works.
>
>And I believe that this sub can give me some good ideas about VBA programming with ranges.
>
>Could you please explain to me (step by step) how this sub works!
>
>Thanks in advance!
>
>regards Torstein
>
>________________________________
>Fra: Torstein Johnsen <sejohnse@yahoo.no>
>Til: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sendt: Mandag, 2. juli 2012 11.34
>Emne: Vedr: [ExcelVBA] Select range in column with non-continuous data
>
>
>
>David and Derek!
>
>Thanks to both of you for both manual and VBA solutions, which are both useful in different situations.
>
>That's wonderful with these groups, a lot of people sharing lots of Excel knowledge and experience.
>
>regards Torstein
>
>________________________________
>Fra: David Grugeon <yahoo@grugeon.com.au>
>Til: ExcelVBA@yahoogroups.com
>Sendt: Søndag, 1. juli 2012 1.58
>Emne: RE: [ExcelVBA] Select range in column with non-continuous data
>
>
>
>I would select B1 then, holding SHIFT down press END the HOME. Then, still
>holding SHIFT down use the left arrow until only one column is selected.
>
>Easier to do then describe!
>
>Best Regards
>David Grugeon
>Excel VBA Group Moderator
>
>-----Original Message-----
>From: mailto:ExcelVBA%40yahoogroups.com [mailto:mailto:ExcelVBA%40yahoogroups.com] On Behalf
>Of Torstein Johnsen
>Sent: Wednesday, 27 June 2012 7:32 PM
>To: mailto:ExcelVBA%40yahoogroups.com
>Subject: [ExcelVBA] Select range in column with non-continuous data
>
>This is not a VBA-question.
>
>I hope I can explain it and get some good advice anyway.
>
>in a worksheet - 3 columns, about 10000 rows of data.
>In column A and C the data are continuous - all cells are filled with data
>In column B - some cells are empty.
>
>I want to select the range B1.BNNNN (where NNNN is the last row with data),
>but it's not possible to use END-DOWN because of the empty cells in that
>column.
>
>Is there any way around?
>
>Thanks in advance!
>
>Torstein
>
>[Non-text portions of this message have been removed]
>
>------------------------------------
>
>----------------------------------
>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/
>
>----------------------------------Yahoo! Groups Links
>
>[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]
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
----------------------------------
Tidak ada komentar:
Posting Komentar