ExcelBanter - Find column number of last used cell in a row

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find column number of last used cell in a row (https://www.excelbanter.com/excel-programming/434181-find-column-number-last-used-cell-row.html)

Find column number of last used cell in a row

I want to dynamically create ranges beginning in Row 1 but I am having
trouble finding the last column. There are other cells in use on Row 1 in
the worksheet so I don't want to use Last Column or Last Cell or start from
the rightmost column and use xlToLeft. I can find the LastRow easily enough

I thought starting in A1 and going ri9ght and select would do it but that
isn't working.

FinalColumn = Cells(Selection.Selection.End(xlToRight)).Select

Any suggestions?

Thanks

Find column number of last used cell in a row

The problem you have is your final .Select is a method that performs an
action and cannot assign an action to a variable.

FinalColumn = Range("A1").End(xlToRight).Column

I wish you had posted a larger bit of your code because it looks like you
may be selecting cells before operating on them... this is usually
unnecessary and inefficient to do. Perhaps this previous posting of mine (a
response to another person using Select/Selection type constructions) will
be of some help to you...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)

"KG Old Wolf" wrote in message
...
I want to dynamically create ranges beginning in Row 1 but I am having
trouble finding the last column. There are other cells in use on Row 1 in
the worksheet so I don't want to use Last Column or Last Cell or start
from
the rightmost column and use xlToLeft. I can find the LastRow easily
enough

I thought starting in A1 and going ri9ght and select would do it but that
isn't working.

FinalColumn = Cells(Selection.Selection.End(xlToRight)).Select

Any suggestions?

Thanks

Find column number of last used cell in a row

Don & Rick -

Thank you for your recommendations and explanations. It is wonderful to
have such knowledgable resources so readily available. I am learning VBA -
self taught. I will try to keep my questions to a minimum but it's great you
are "here".

Ken

Find column number of last used cell in a row

Follow-up question.

Your code works just as I hoped it would (TY) but I am confused why I can't
seem to create similar code for rows

i.e. LastRow = Range("A1").End(xlDown).Rows

instead, I am using the following to get the row count but I believe this
will give me the very last row in use on the sheet which is not the same
thing (even though it happens to work in my case).

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

In fact, the inability to code similar lines is why I posted my original
request for help... I could get the rows with the above but changing it to
get the columns wouldn't work (and I don't know why!)

Thanks,
Ken

---------------------------

"Rick Rothstein" wrote:

The problem you have is your final .Select is a method that performs an
action and cannot assign an action to a variable.

FinalColumn = Range("A1").End(xlToRight).Column

I wish you had posted a larger bit of your code because it looks like you
may be selecting cells before operating on them... this is usually
unnecessary and inefficient to do. Perhaps this previous posting of mine (a
response to another person using Select/Selection type constructions) will
be of some help to you...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)

"KG Old Wolf" wrote in message
...
I want to dynamically create ranges beginning in Row 1 but I am having
trouble finding the last column. There are other cells in use on Row 1 in
the worksheet so I don't want to use Last Column or Last Cell or start
from
the rightmost column and use xlToLeft. I can find the LastRow easily
enough

I thought starting in A1 and going ri9ght and select would do it but that
isn't working.

FinalColumn = Cells(Selection.Selection.End(xlToRight)).Select

Any suggestions?

Thanks

Find column number of last used cell in a row

Forget the question below - I found the problem.....

Thanks,
Ken

"Rick Rothstein" wrote:

The problem you have is your final .Select is a method that performs an
action and cannot assign an action to a variable.

FinalColumn = Range("A1").End(xlToRight).Column

I wish you had posted a larger bit of your code because it looks like you
may be selecting cells before operating on them... this is usually
unnecessary and inefficient to do. Perhaps this previous posting of mine (a
response to another person using Select/Selection type constructions) will
be of some help to you...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)

"KG Old Wolf" wrote in message
...
I want to dynamically create ranges beginning in Row 1 but I am having
trouble finding the last column. There are other cells in use on Row 1 in
the worksheet so I don't want to use Last Column or Last Cell or start
from
the rightmost column and use xlToLeft. I can find the LastRow easily
enough

I thought starting in A1 and going ri9ght and select would do it but that
isn't working.

FinalColumn = Cells(Selection.Selection.End(xlToRight)).Select

Any suggestions?

Thanks