don't drag the cells down until you know how many rows you need to go. You
can determine that using the following:

Dim lr As Long
lr = Cells(65536, 1).End(xlUp).Row 'assign last row of data to variable
Selection.AutoFill Destination:=Range("A46:" & "A" & lr) ' fill down using
variable

HTH
dmoney

"yak141" wrote:

Hi,
I have been importing data from another excel sheet. I use ='C:\pc ranch
report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to
let it automatically copy all the data from the other excel sheet.

However, The data from the other excel sheet is automatically generated, so
it might have different number of columns. That's why I dragged the cells
down a few more rows than the original excel file has to leave extra space
just in case there might be more columns generated in the next generation.
The extra space that I dragged shows 0 or #REF.

Can I add something in front of the ='C:\pc ranch
report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells
won't show up as 0 or # Ref??
Thank you so much!!!