Can someone help me understand why I get error 1004 from this script?
I am trying to find the first empty cell in colum A and select it so that I can paste additional data. It fails at the Range(KSB1_Hdr).Select

Dim KSB1_lastHdr As Integer
KSB1_lastHdr = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count + 1
Dim KSB1_Hdr As String
KSB1_Hdr = "A" & KSB1_lastHdr & ""
Worksheets("KSB1").Select
Range(KSB1_Hdr).Select
Thanks,
Lee

Hi Lee,

Selecting objects can be very buggy in Excel. My first guess would be that your code is in a worksheet's class module (not the "KSB1" worksheet) which means that your unqualified range property will refer to the range on that worksheet rather than the "KSB1" worksheet. The range object is by design strictly 2 dimensional in the Excel Object Model, so if the named range "KSB1_Hdr" is on the KSB1 worksheet, but your range property is implicitly qualified to a different worksheet, you'll get an error.


If I've successfully identified the problem there are 3 workarounds:

1. You can qualify your range property:
2. Leave the code the same but move your subroutine to a standard code module so the unqualified range property will reference the activesheet.


3. Best option!
The bottom line is that 99% of the time we don't need to select ranges - this normally just slows your code down and makes it prone to errors unless you code it very carefully. What do you intend to do with the range, we should be able to show you a better way of doing this?


Hope that helps....

I think it is likely you are exceeding the limit of the "Integer" Data Type by the number of rows. So to start with you should Type "KBS1_hdr" varible as a Long number vs the integer.

Now you can do something like this to work better: