Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More VBA Help please
I can use this code;
ActiveSheet.UsedRange.Cells.Select MsgBox ActiveSheet.UsedRange.Cells.Address and the message gives $A$1:$E$8 - which is correct Can I parse out (or programatically get) the Start ($A$1) and End ($E$8) Address values ? How would I (for instance) Set a Range to those values ? Can I further parse out the actual Row/Column Elements (giving me 4 seperate values) so I could reconstruct a new Range with calculated & actual values ? Not used to working with vba too much so trying to get a grip on how things work. Thanks for any help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
More VBA Help please
On 22 Jul 2013 12:04:10 GMT, Malcolm Hind wrote:
I can use this code; ActiveSheet.UsedRange.Cells.Select There is usually no need to use the Select method. MsgBox ActiveSheet.UsedRange.Cells.Address and the message gives $A$1:$E$8 - which is correct Can I parse out (or programatically get) the Start ($A$1) and End ($E$8) Address values ? With ActiveSheet.UsedRange Set rStart = .Item(1) Set rEnd = .Item(.Count) End With How would I (for instance) Set a Range to those values ? Referring back to my last post, instead of set MyRange = Selection do set MyRange = ActiveSheet.UsedRange or set MyRange = range(rstart,rend) Can I further parse out the actual Row/Column Elements (giving me 4 seperate values) so I could reconstruct a new Range with calculated & actual values ? Something like Dim StartRow As Long, StartCol As Long, EndRow As Long, EndCol As Long With ActiveSheet.UsedRange StartRow = .Item(1).Row StartCol = .Item(1).Column EndRow = .Item(.Count).Row EndCol = .Item(.Count).Column End With Not used to working with vba too much so trying to get a grip on how things work. Thanks for any help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
More VBA Help please
This might be a bit complex for you, but I find it the easiest way to
handle individual values (ergo 'elements') of a range of value/data... Dim vDataIn, n&, k& vDataIn = ActiveSheet.UsedRange For n = 1 to UBound(vDataIn) '//iterate each row For k = 1 To UBound(vDataIn, 2) '//iterate each col Debug.Print vDataIn(n, k) '//do stuff with each element Next 'k Next 'n -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
More VBA Help please
GS wrote in :
This might be a bit complex for you, but I find it the easiest way to handle individual values (ergo 'elements') of a range of value/data... Dim vDataIn, n&, k& vDataIn = ActiveSheet.UsedRange For n = 1 to UBound(vDataIn) '//iterate each row For k = 1 To UBound(vDataIn, 2) '//iterate each col Debug.Print vDataIn(n, k) '//do stuff with each element Next 'k Next 'n Thanks Ron and to you GS, I again have a couple of follow ups that I will post seperately. Thanks for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|