End(xlUp) only works when the sheet is acive

Question

I have following code

Set wb = ThisWorkbook`

ComboBox7.RowSource = wb.Worksheets("Sheet5").Range("A2", _
Range("A65536").End(xlUp)).Address

If I don't put wb.Sheets("Sheet5").Select before this line, this code throws error

"Application defined or object-defined error"

I want this code to work without selecting the Sheet5.

If I put ComboBox7.RowSource = wb.Worksheets("Sheet5").Range("A2:A7").Address then it works fine without selecting the sheet5.

Is there any way to use End(xlUp) without selecting the sheet?

score 5 · Accepted Answer · answered May 01 '13 at 08:57

5

Yes it is possible.

Logic: Find the last row and then use that to create a range which you can assign to your combobox.

Is this what you are trying?

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet5")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("A1:A" & lRow)
    End With

    ComboBox5.RowSource = rng.Address
End Sub

answered May 01 '13 at 08:57

Siddharth Rout

147,039
17
206
250

I will be using this thing in many places. If i convert this to a function, how can I pass the Column name to this function and use it in the function? – mayur2j May 01 '13 at 09:19
Will it be from the same sheet always? – Siddharth Rout May 01 '13 at 09:20
Hey the above code doesn't throw an error but it is taking the range from the sheet1 and not sheet5 – mayur2j May 01 '13 at 09:32
Not possible :) What have you specified here? `Set ws = wb.Sheets("Sheet5")`? – Siddharth Rout May 01 '13 at 09:34
May I see your file? It is technically impossible for the code to set the range to Sheet1 if you have specified Sheet5 – Siddharth Rout May 01 '13 at 09:45
The selected sheet is `Sheet1` tha'ts why its taking `range` from `Sheet1`. I debugged it the `lRow` is coming correct according to the data in Sheet5 but in `combobox` it is showing values from `Sheet1`. Sorry i can't share the code. Are you online right now? – mayur2j May 01 '13 at 09:53
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/29235/discussion-between-mayur2j-and-siddharth-rout) – mayur2j May 01 '13 at 09:58
I prefer to use `sheet.cells.find("*", direction:=xlprevious)` – glh May 01 '13 at 21:59
@glh: Even me too but only if I am trying to find the last row in a worksheet... not in a column. You might want to see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – Siddharth Rout May 01 '13 at 22:10

score -1 · Answer 2 · edited Aug 23 '13 at 22:07

The other thing is to do this in one line

Private Sub CommandButton1_Click()
Set wb = ThisWorkbook
Set ws = wb.sheets("sheets5")

ComboBox7.RowSource = ws.Range("A2", ws.Range("A65536").End(xlUp)).Address

The second range thinks it is working from the active sheet, you need to tell it otherwise.

Using With is better but this is one line if you are writing code quickly just to get something done.

End(xlUp) only works when the sheet is acive

2 Answers2