extend selection to last used column vba

Question

Code :

Sub MyColumnSelect()
Dim myCurrentRow As Long
Dim myLastColumn As Long
myCurrentRow = ActiveCell.Row
myLastColumn = ActiveCell.SpecialCells(xlLastCell).Column
Range(ActiveCell, Cells(myCurrentRow, myLastColumn)).Select
End Sub

I need to extend my selection from any colum or any row to the last used column in the sheet. This code extends the selection to a particular column, in my case to column P. I don't know why is this the case but even if there are only 4 columns, it still extends the selection to column P.

Image :

PS: I know I shouldn't use select, but I can't make it work even with select. Need Help.

Use usedrange.columns.count.... Here you can find information to study on this: https://stackoverflow.com/questions/21172108/activesheet-usedrange-columns-count-8-what-does-it-mean. — JvdV, Jul 04 '18 at 09:09

TinMan · Accepted Answer · 2018-07-05T19:05:48.443

2

Ron de Bruin - Find last row, column or last cell show you how to find the last used column.

Sub MyColumnSelect()
    Dim myCurrentRow As Long
    Dim myLastColumn As Long
    myCurrentRow = ActiveCell.Row
    myLastColumn = Cells.Find(What:="*", After:=Cells(1, 1), _
                        Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, MatchCase:=False).Column

    Range(ActiveCell, Cells(myCurrentRow, myLastColumn)).Select
End Sub

This also works. But beware that UsedRange can sometimes extend past the data do to non-continuous formatting.

  Intersect(ActiveCell.EntireRow, ActiveSheet.UsedRange).Select

Addendum

As @Rawrplus pointed out using ActiveCell and Selection are bad habits and should be avoided. Watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

edited Jul 05 '18 at 19:05

answered Jul 04 '18 at 09:39

TinMan

6,624
2
10
20

Yeah same thing was happening with my code. once I use the code in a data of 4 columns, that last used column will be 4, but when I change the data, still the selection is till 4 columns. Yours worked perfectly. Thankyou so much. I do have one question though, I have same problem in last used row, can U give me answer for that too or should I ask another question. ? – Aman Devrath Jul 04 '18 at 09:43
1

Read [Ron de Bruin - Find last row, column or last cell](https://www.rondebruin.nl/win/s9/win005.htm). It's `Select Case 2` in `Function Last(choice As Long, rng As Range)`. – TinMan Jul 04 '18 at 09:47
@TinMan Yuck!!! `ActiveSheet`, `.Select` `On Error Resume next` inside code.. Damn, don't be so cruel and make him learn bad beginner coding habits that he will have to forcefully unlearn. I absolutely would **not** recommend studying from those excerpts! – Samuel Hulla Jul 04 '18 at 16:52
@Rawrplus I added a disclaimer. I hesitated in answering this one but decided to because I have hot keyed similar code before. Being able to extend the selection like this can be very handy while formatting and editing large worksheets. – TinMan Jul 05 '18 at 19:09

score 1 · Answer 2 · answered Jul 04 '18 at 09:07

1

Bit of googling would have absolutely yielded an answer. This is the most common way of retrieving the last actively used row

Shets("Sheetname").Cells(Rows.Count, <inColumn>).End(xlUp).Row

and column

Shets("Sheetname").Cells(<inRow>, Columns.Count).End(xlToLeft).Column

In your case, presuming your sheet is named "Sheet1" it would be:

Dim lr as Long
Dim lc as Long
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
lc = Sheets("Sheet1").Cells(4, Columns.Count).End(xlToLeft).Column

Obviously, if your column ranges vary, you should retrieve them in a loop (eg. loop through all the active rows)

For i = 3 to lr
   lc = Sheets("Sheet1").Cells(i, Columns.Count).End(xlToLeft).Column
   ' do something
Next i

Also, do absolutely read up on why you should not use Select, ActiveSheet and so on..

answered Jul 04 '18 at 09:07

Samuel Hulla

6,617
7
36
70

Thankyou for your answer, but still couldn't make it work in my code. – Aman Devrath Jul 04 '18 at 09:16
It does work.. you're just not executing anything I presume. Eg do `Debug print lc` after the lc assignment in for loop or check if your sheet is named "Sheet1". Can't give you a better answer than that with the info you provided in your question – Samuel Hulla Jul 04 '18 at 09:20
I meant, I couldn't integrate your solution in my answer to extend selection from activecell to the last used column. Thanks again. I'll try it. – Aman Devrath Jul 04 '18 at 09:28
@Rawrplus: D'accord with not to use `Select` at all. But what else except `ActiveSheet` should be used if the macro shall run in the current active sheet? – Axel Richter Jul 04 '18 at 09:41
@AxelRichter I do get your point. Though the thing is, in large majority of cases, the programmer will know to which Sheet will the code apply to. I'm not necessarily implying you should never ever use `ActiveSheet`. Actually for that matter, even `Select` could have it's uses - what I am however saying is, that it is a bad habit which can almost always be avoided and should be used when only absolutely necessary (which almost never is) – Samuel Hulla Jul 04 '18 at 09:57

score 1 · Answer 3 · answered Jul 04 '18 at 09:32

Try this:

Sub MyColumnSelect()

Dim myRow, myLastColumn As Long

 For myRow = 3 To ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

  myLastColumn = ActiveSheet.Cells(myRow, Columns.Count).End(xlToLeft).Column

  ActiveSheet.Range(Cells(myRow, 1), Cells(myRow, myLastColumn)).Copy

 Next

End Sub

extend selection to last used column vba

3 Answers3

Addendum