VBA. Determine last column used

[rain4u]

Hi I have a macro code as below

Please Login or Register  to view this content.

Essentially what this code does is taking information across 10 sheets and then populates the information in a meaningful way. The code itself works just fine but its only very resently I realised it has a flaw to determine the last column used (lcol).

Please Login or Register  to view this content.

The section above only determines the last column as per row 2. I would need the code to check the last column usage as far as the data goes (e.i. across all rows as per column B). Or in simple terms, the code should understand which is the furthest column that has any sort of data in it.

I have included an example workbook with the code above along with some comments.


I would be extremely thankful if someone could help me to get it right.
Cheers
Rain

[jason.b75]

One way

Please Login or Register  to view this content.

[arlu1201]

Try this

Please Login or Register  to view this content.

[rain4u]

Thank you Jason, Thank you Arlu1201

Jason
I used the line you provided with small modification

Please Login or Register  to view this content.

I think with the example workbook, the textboxes, arrows etc affected the UsedRange. Before I used the amended code it not include the information as per last column used (it excluded the last column). Plus 1 (+ 1) did the trick. Thank you for your help. Cheers

[arlu1201]

The usedrange may not always give you the right answer.

[jason.b75]

The usedrange may not always give you the right answer.

I agree, having no data in column A was something that I didn't take into consideration with my suggestion.

Please Login or Register  to view this content.

as suggested by Arlu is the preferable option.

[rain4u]

Hi arlu1201, jason.b75
I get Run-time error '1004': Application-defined or object-defined error with the code below.

Please Login or Register  to view this content.

it stops at

Please Login or Register  to view this content.

What am I missing? I would appreciate any help.
Cheers
Rain

[jason.b75]

You're trying to use the value of j before it has been given a value.

[rain4u]

Thanks for quick response Jason.

I have to admit I'm still light years away of ever really understanding VBA. Just when I think I have reached some sort of beginner level the whole house comes down. I'm such a newb

I moved "If Worksheets("Data_shift_HOLS").Cells(i, j).Value <> "" Then" down by two lines like this:

Please Login or Register  to view this content.

It now stops at

Please Login or Register  to view this content.

It almost feels like catch 22 where I can not figure out how to change one thing without affecting the other.
Any chance you could correct my code?
I would be very grateful. Sorry to be bit of pain.

[jason.b75]

You had 2 lines around the wrong way, see how this goes,

Please Login or Register  to view this content.

[rain4u]

Looks to be working a treat.
Many thanks