Error in Else part of If statement, but Else shouldn't activate. Trying to use xlDown on blank worksheet

Question

I'm attempting to copy material from one workbook and paste it into the next open cell in a different workbook.

I've tried using xlDown, but a runtime error occurs when the sheet is blank because there are no used range of non-empty cells to navigate to the bottom of. So I set up an If Else Statement to determine if cell A1 is empty or not. If A1 is empty, select it, else use xlDown to get to the last cell.

Range("a1").Activate
    If IsEmpty("A1") = True Then
        currentCell.Select
    Else
        Range("A1").End(xlDown).Offset(1, 0).Select
    End If

I would expect the code to just select A1 if it's blank the paste the contents that were copied. If A1 isn't blank, I would want to activate or select the next blank cell in Column A.

Thank you in advance for any and all help.

If IsEmpty(range("A1")). If A1 isn't blank but everything else is xldown will take you to the bottom of the sheet and you will get an error. Better to work up from the bottom. And don't select things stackoverflow.com/questions/10714251/… — SJR

Charlie Rodiles Charlie Rodiles · Accepted Answer · 2019-07-31T15:51:53

First of all, you should always avoid using Select or Activate, but answering your question directly:

If Range("A1") = "" Then
    Range("A1").Select
Else
    Range("A1").End(xlDown).Offset(1, 0).Select
End If

If A1 is empty (""), select it. Otherwise, select the last cell using xlDown (There's also a better way of finding the last row).

Error in Else part of If statement, but Else shouldn't activate. Trying to use xlDown on blank worksheet

1 Answers