xlFillDefault, based on values in ColA to ColZ

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I guess I’m not going about this the right way. I’m trying to find the last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want to
copy down A16:Z16 into A17:Z17.

Here’s the code I’m trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: ‘AutoFill method of range class failed’
I guess the range is not defined properly.

I’m completely open to suggestions as to how to do this.

Thanks!
 
Thanks JLGWhiz. I tried that. Still doesn't work.
Any other ideas? I have some cells with Data Validation and some cells with
formatting applied. This is why I want to select a range and FillDown.
 
You're specifying the worksheet when you find the dynarow, but you're relying on
the current selection in the next line.

I'd use:

Dim dynarow As Long
with worksheets("Worksheet")
dynarow = .Cells(.Rows.Count, "Z").End(xlUp).Row
.cells(dynarow,"A").resize(1,26).AutoFill _
Destination:=.cells(dynarow,"A").resize(2,26), Type:=xlFillDefault
end with

If that doesn't help, I'd make sure that dynarow was what I thought it was
supposed to be by adding a
Msgbox dynarow
before the autofill line.
 
Don't understand why you still got the message. I only changed the sheet
name to one of my sheets and deleted the xlFillType type because it
alutomatically uses default. it no other type is specefied. I listed a row
of data on row 16 and ran the macrow. It filled in row 17, A - Z. But, it
did show that the constant xlFillDefault is not recognized by that internal
class. There are a lot of those out there. Some programmer used the
constant in their write op of how it works, but omitted it from the class
when it was developed.
 
Thanks Dave! That did it!! I think the source and the destination have to
be the same size. I read something about this today; can't find the link
now. I tried all sorts of things but nothing worked until I tried your code
Dave!

Thanks again!!
Ryan---
 
The source and destination aren't the same in this code:
.cells(dynarow,"A").resize(1,26).AutoFill _
Destination:=.cells(dynarow,"A").resize(2,26), Type:=xlFillDefault
Click to expand...

The .resize(1,26) says to start with 1 row by 26 columns.

The .resize(2,26) gives you 2 rows by 26 columns.

I still think it's got something to do with the worksheet or the selection.

If the correct range were selected, I think your original code would have worked
fine. But I'd do my best not to rely on that selection being correct.
 
Back
Top