Simple code not working on VBA - Help me pls

Question

I am facing a problem with the line of code not working as highlighted. The error code 1004 keeps popping up. I tried using activate around the codes but to no avail. I tested the line of code in the immediate window and it works.

No clue as to why there is an issue with the codes.

Answer 1

Regardless of your macro recorded code, this sometimes happens in some versions and builds of Excel. Sometimes the "Select" method of Worksheet object doesn't work exactly the same way as in other Excel version and you have to use Activate method instead.

Instead of:

    ThisWorkbook.Sheets("FX rate").Select
    Range("A1").Select

try this:

    ThisWorkbook.Worksheets("FX rate").Activate
    Range("A1").Select

Answer 2

This can happen if your code is in a sheet object instead of a module.

The quickest fix is to change Range("A1").Select to ThisWorkbook.Sheets("FX rate").Range"A1").Select but you will still have more problems further down the rest of your code that you haven't posted.

A better fix is to move your code to a module, use With / End With, or follow @Ike's link to avoid using select.

Answer 3

You really should try to avoid using Select entirely. Recoding a macro will produce this code but you should then alter the code to remove it. Even when done perfectly, it can go badly for instance if the user clicks the screen while it's running.

For the first part of your example, you could do the following:

With ThisWorkbook.Worksheets("Nostro bals - 5010")
    
    .Range("A2:F1000").ClearContents

End With

For the second part, your code is finding the bottom of the data by using the equivalent of CTRL-Down from A1. This means if there is a break in the data, it will only clear down to that row. If that is your intention, then you can use the following:

With ThisWorkbook.Worksheets("FX rate")
    
    ex_end_row = .Cells(1, 1).End(xlDown).Row
    .Range("A3:B" & ex_end_row).ClearContents

End With

However, if you actually want it to find the last cell in column A with data in it, regardless of breaks etc, then you could use this, which starts at the very bottom of column A and then uses CTRL-Up:

With ThisWorkbook.Worksheets("FX rate")

    ex_end_row = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A3:B" & ex_end_row).ClearContents

End With

Note, with either of these, you should declare your ex_end_row beforehand:

Dim ex_end_row As Long