Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find column number of last used cell in a row
finalcolumn = Cells(Selection.Row, Selection.Column).End(xlToRight).Column
MsgBox finalcolumn Range(Selection, Cells(Selection.Row, Selection.Column).End(xlToRight)).Select -- Don Guillett Microsoft MVP Excel SalesAid Software "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the last number of a column (lower cell) | Excel Discussion (Misc queries) | |||
FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
Find previous number and find next number in column | Excel Discussion (Misc queries) | |||
Find DMIN in a column range determined by a number in another cell | Excel Worksheet Functions | |||
Find cell below last number in a column | Excel Programming |