Solved: newbie wanting to know how to selct a variable range in VBA

[divingdingo]

i'm extremely new to this VBA programming. in fact so new i'm only just working myself through my first book. "VBA for dummies"!!

anyway i have a few specific reasons why i want to be able to adapt excel to start automating many of the repetative functions i continuously do on data.

i want to add a column to every spread sheet (with my data on) copy another column then format it all the same.

i can actually do this with what i have learnt so far. my problem is this:

every workbook that comes to me has a different number of rows. therefore when i write the code i need to select a different number of rows as the range to format each time. could someone please point me in the right direction to just select a range that extends to just the number of rows with data in.

does this make any sense to anyone ?!

thank you for you help in advance


mark

[uksrogers]

I would look at the ActiveSheet.UsedRange function.

Alternatively there is something called SpecialCells that will give you the last cell on the sheet.

You should be able to find the syntax information in the VBA Help.

uksrogers

[RECrerar]

Couple of ways, if there are no gaps in the data you could use

Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select

Assuming that assuming that the data starts in cell A1 (change the cell and column references as required).

Also CountA is quite useful.

Just a word of advice, you have posted this on VBA Excel help forum (http://www.excelforum.com/showthread.php?t=622851). People don't like cross posting as it means they can spend time working on problems that have already been solved. It is in the rules and you are likely to get your threads closed if you do cross post.

Regards

[divingdingo]

Couple of ways, if there are no gaps in the data you could use

Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select

Assuming that assuming that the data starts in cell A1 (change the cell and column references as required).

Also CountA is quite useful.


Regards

thanks very much for the responses guys.

i'm finding out very quickly in VBA that there is loads of different was to skin a cat.

firstly i tried a do ... loop until command. but found that it took too long in processing time due to the number of rows i have in my spreadsheet.

then i found a solution in the way of introducing a

variable = selection.rows.count.

then autofilled the returned string.
bingo!!

Just a word of advice, you have posted this on VBA Excel help forum . People don't like cross posting as it means they can spend time working on problems that have already been solved. It is in the rules and you are likely to get your threads closed if you do cross post.

Regards

opps, sorry about that. i'm new to these forums and was wondering which to post to. i appologise and understand the reason.

thanks again for the advice in code and the forewarning in forum etiquette.

mark

[Bob Phillips]

Cross-posting is not considered bad per se by everyone (you always will get net nannies who complain when someone doesn't behave in the way that THEY think is the only acceptable behaviour), just let both forums know you have posted to the other so a responder can check if it has already been answered and not waste time.

[divingdingo]

wow, finding a solution to one thing, and then sudden there's another problem with similar theme !! so is this how VBA is going to be from now on. problem after problem.

What fun!!!

problem 2:

i want to select and rename a range from b1 to the last cell of my spreadsheet to be "whatever"

the last cell of my spreadsheet changes depending on which workbook i open so i have to write code for it to establish this then select range then rename selection.

i was thinking of XLcelltypelast cell for a while but i couldn't find much help.

i wonder if anyone knows an easy way of doing this?

thanks

mark


p.s i haven't posted on over threads this time !

[Bob Phillips]

[vba]

Range(Range("B1"), Range("A1").SpecialCells(xlCellTypeLastCell)).Name = "myRange"
[/vba]

[divingdingo]

i was so excited yesterday for getting all the code to work that i forgot to thank you for your help.

it was very much appreciated xld.

take care

mark