Further technique help required, please!

[londresw]

Problem 1

If I have a block of data with variable height and width, how can I have VBA work out how wide that block is, for selection purposes?

i.e.

It's relatively easy to select a row which is 5 wide, because I know that I just have to do a range from say A1:E1 but if I don't know in advance how wide the block is going to be, but will always need entire width of the data... how can I work this out?

Problem 2

I need to know how to change the names of sheets?

e.g. change "sheet2" to "International"

and also, how to create new sheets?

Problem 3

If I have a body of data that I've pasted into say 'sheet2', how do I go to the row 1 beneath the bottom left of that body, so as to paste more, potentially, later?



I'm sorry to be a pain, I know these things will be easy to someone but I'm struggling as I'm new. I'm enjoying learning though and want to succeed in my brief so all help greatly appreciated.

Cheers all.

[CreganTur]

Problem 1

If I have a block of data with variable height and width, how can I have VBA work out how wide that block is, for selection purposes?
You can use a dynamic named range to accomplish this. The picture below shows a dynamic named range I created that works for width- it starts at G2 and will include all columns in Row 2 that contain data.

http://img58.imageshack.us/img58/4397/namedrangesey6.png
Problem 2

I need to know how to change the names of sheets?

e.g. change "sheet2" to "International"

The following code snippet adds a new worksheet and will name it "SheetName"

Sheets.Add
ActiveSheet.Name = "SheetName"

Problem 3

If I have a body of data that I've pasted into say 'sheet2', how do I go to the row 1 beneath the bottom left of that body, so as to paste more, potentially, later?

This will get the last used cell in row A:

LastRow = .Range("A65536").End(xlUp).Row

Then you can refer to this to get the first unused cell:

Range("A" & LastRow + 1)

[londresw]

Thank you, that's all worked so far tried Answers to problems 2 and 3.

Answer to problem 1 sounds a bit difficult so might have to hope someone else gets back to me with a simpler method! Otherwise, might try that in a few days when I've got everything else under wraps.

Really appreciate your help

[Bob Phillips]

Range("A1").CurrentRegion

[londresw]

Range("A1").CurrentRegion


are you sure this is right?

doesn't seem to do much?

:dunno

[londresw]

ok, i have that now, thanks. i wanted specifically to be able to find out how wide it was though.

i.e. if the block runs from a1 to e3 it would return '5' ?

[Bob Phillips]

rng.Columns.Count

[londresw]

:joy:

Thank you very much.