xlFillDefault, based on values in ColA to ColZ
in [Programming]
|
From: ryguy7272 on 10 May 2010 11:20 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! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.
From: JLGWhiz on 10 May 2010 13:54 Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine. "ryguy7272" <ryguy7272(a)discussions.microsoft.com> wrote in message news:72C9509B-27E6-4010-B909-60A22BB66435(a)microsoft.com... >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! > > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 10 May 2010 17:13 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. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "JLGWhiz" wrote: > Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine. > > > > "ryguy7272" <ryguy7272(a)discussions.microsoft.com> wrote in message > news:72C9509B-27E6-4010-B909-60A22BB66435(a)microsoft.com... > >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! > > > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > . >
From: Dave Peterson on 10 May 2010 17:34 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. ryguy7272 wrote: > 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! > > -- Dave Peterson
From: JLGWhiz on 10 May 2010 18:06
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. "ryguy7272" <ryguy7272(a)discussions.microsoft.com> wrote in message news:4D4EBBC9-3B98-484E-B6C4-ABD02211E6BF(a)microsoft.com... > 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. > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "JLGWhiz" wrote: > >> Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine. >> >> >> >> "ryguy7272" <ryguy7272(a)discussions.microsoft.com> wrote in message >> news:72C9509B-27E6-4010-B909-60A22BB66435(a)microsoft.com... >> >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! >> > >> > >> > -- >> > Ryan--- >> > If this information was helpful, please indicate this by clicking >> > ''Yes''. >> >> >> . >> |