CODEWITHSUNDEEP
excelrangevba
mayur2j
mayur2j

Reputation: 141

End(xlUp) only works when the sheet is acive

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?

Upvotes: 1

Views: 2388

Answers (2)

keith whatling
keith whatling

Reputation: 1

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.

Upvotes: -1

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 5

Related Questions