Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel in Office XP- Range/Selection Object

Status
Not open for further replies.

drkestrel

MIS
Sep 25, 2000
439
GB
This is what I am trying to do

1) User manually select a random range in Excel for Office XP (e.g. A8:M10, A20:M20, A30:M:32)

2) User click a VBA Button
2.1 For each row in time
2.1.1) Code should modify the first "column" of the row
2.1.2) Code should set the "Pattern" of the row to grey
2.1.3) On error, stop processing subsequent rows (rows processed could stay as is)

How could I find out the Range "co-ordinates", I think I need the absolute range (because after processing each row, I want to highlight that row Grey (which would lose my selection). How could this be done?
 
You can use syntax like this to pick up the 4 corners of your selection - then use the cells syntax ( cells(row,col) ) to reference the rows and columns

stRw = selection.rows(1).row
enRw = selection.rows(selection.rows.count).row
lCol = selection.columns(1).column
rCol = selection.columns(selection.columns.count).column

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Given objCurWorksheet as the Worksheet object that I am working on, how could I find out obtain the Range object of what the user has selected?
 
give a man a fish and he'll eat for a day, teach him how to fish etc etc etc. I'm quite disapointed here that you cannot breach the small gap between what I have given you and what you need.

dim SelRange as range, startRow as long, endRow as long, startColumn as integer, endColumn as integer
with objCurWorksheet.Selection
set selRange = range(.address)
startRow = .rows(1).row
endRow = .rows(.rows.count).row
startColumn = .columns(1).column
endColumn = .columns(.columns.count).column
msgbox selRange.address
msgbox "Start Row is:" & startRow & vbcrlf & "End Row is:" & endRow & vbcrlf & "Start Column is:" & startcolumn & vbcrlf & "End column is:" & endColumn
end with

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top