Application defined or object defined Error on End(xlDown)

Question

I am getting an error Application defined or Object defined error when I run the below code. Error is highlighted at line Range("B2").End(xlDown).Offset(1, 0).Select

I even tried using Sheets("Sheet5").Range("B2").End(xlDown).Offset(1, 0).Select but it still gives the same error. Any help is much appreciated.

Worksheets("Sheet5").Select

Range("B2").End(xlDown).Offset(1, 0).Select

My full sub-

Sub test() 
    Workbooks("OC.xlsm").Activate 
    Worksheets("Sheet3").Select 
    Range("C13:E13").Copy 
    Worksheets("Live").Select
    Range("B2").End(xlDown).Offset(1, 0).Select 
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 
    Application.OnTime Now + TimeValue("00:00:10"), "test" 
End Sub

Sub test() Workbooks("OC.xlsm").Activate Worksheets("Sheet3").Select Range("C13:E13").Copy Worksheets("Live").Select Range("B2").End(xlDown).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Application.OnTime Now + TimeValue("00:00:10"), "test" End Sub — Nick, Jan 28 '21 at 04:21
Any particular reason why you're copying three cells and then pasting that to a range on which you are applying xlDown ? Otherwise, you should get rid of all calls to Select, they are useless. — ApplePie, Jan 28 '21 at 04:43
[This is how to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jan 28 '21 at 05:17
And also [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Jan 28 '21 at 05:28
If `Range("B2").End(xlDown)` goes to the bottom of the sheet then you can't do `.Offset(1, 0).Select` from there — Tim Williams, Jan 28 '21 at 05:48
Thank you all! I figured out another way instead of using Offset. I used a for loop to increment the rows so it updates the chart. — Nick, Jan 28 '21 at 08:19
Its better to go from bottom of file to last row data and then offset: `Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select` — Salamander Krajza, Jan 28 '21 at 08:22

Application defined or object defined Error on End(xlDown)

0 Answers0