More VBA Help please

M

Malcolm Hind

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
 
R

Ron Rosenfeld

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
 
G

GS

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
 
I

Isis

GS said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top