Identifying Last Column in Data Set

[Gard5096]

I am trying to get this loop to place the values from the MAX function into the same row but in the column 1 past the last column in the data set. My trouble comes with identifying the correct column: ".Cells(Columns.Count, "1").End(xlRight).Column + 1"

Please keep in mind that the active worksheet will be filtered down prior to this loop.

Any help will be very much appreciated.

Please Login or Register  to view this content.

Thank you,

Nick

[Gard5096]

OR - Maybe it would be easier to identify the final column and then label it as a variable. Thoughts?

[rscsmith]

Hi Gard

This is a little difficult to test without an example workbook, but here's an attempt:

Please Login or Register  to view this content.

Best regards, Rob.

[Gard5096]

Rscsmith,

Thank you for the quick response. I do have a few questions on your code.

1. Why did you use the cell.resize(,7) ? the worksheet will have more than 7 total columns.

2. Why dd you use the +1 on the bottom part of the If statement?

Thank you again

[rscsmith]

Hi Nick

To answer the questions:
1. I calculate the column I want to write to as the column of "cell" (which is a cell in the range CA2: downwards) + a count of how many cells are used in that row in columns CA:CG. It's the same way you calculate whether there is any data in that dataset. I understood this was where you wanted to write, just after the data in that row. If it is the last column of the entire data set (i.e. all rows), then your second post is correct, i.e. you should identify the last column and assign a variable (otherwise it will keep moving - you identify the last, write one past it, now your new column is the last - it's a loop).

2. Without an example spreadsheet I'm not sure if the +1 is correct. I figured that if it was giving you the result in a column too far right, you could just delete it.

If you could upload a sample workbook with some 'before and after' type notes I could knock it over pretty quickly.

Cheers, Rob.