Macro to understand how wide or long the data range is

[Pekka]

Hello, our golf club has a golf competition with difficult rules. Every
member can play once a week a result and you have approximately 16 weeks time
to improve your score (the sum of your 4 best results). Caddiemasters publish
every second week data of participants´ results and to know situation
(rankings) I have to rearrange data. And it´s boring to repeat some
activities in excel. I can write macros, but now I have to learn a little bit
more.

The data range is every time varying with its width and lenght. The width
varies based on the fact that week amount is raising. The lenght varies based
on amount of players is changing. Macro has to understand, how long the data
range is (calculate row amount, I suppose or understand the number of the
lowest row). Then macro copies first A column with players names and first
week column B, transport the data range to a new data sheet, then copy again
first A column and second week column C and transport the data range to
continut the new data sheet´s pivotable database and so on till we have
played weeks.

 

[Joel]

You need to use xlup,xltoleft,xltoright,xldown

These option stop at blank cells so you have to be careful how you use them.
to ignore the blank cell the following usually works

LastRow = Range("A" & Rows.count).end(xlup).Row

This instruction goes to the last row in column A (rows.count = 65536) and
then moves up until it finds the first cell with data

LastCol = cells(1,columns.count).end(xltoleft).column

This instruction goes to the last column in row 1 (columns.count = 256) and
the moves to the left until it finds the first cell with data.