Use VBA to Autofill a Row until the end of the number of data in another row

%3CLINGO-SUB%20id%3D%22lingo-sub-137261%22%20slang%3D%22en-US%22%3EUse%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-137261%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20need%20some%20help%20with%20the%20following%20problem%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Macro%20should%20select%20the%20first%20cell%20with%20the%20vlookup%20(AY2)%20and%20autofill%20the%20complete%20range%20in%20the%20column%20AY%20until%20the%20last%20row%20that%20contain%20data%20in%20the%20cell%20next%20to%20it%20(Column%20E).%20Column%20E%20is%20the%20cell%20that%20the%20vlookup%20refers%20to.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20situation%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25629i81C4B69B13A91C89%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222017-12-14_09h25_21.png%22%20title%3D%222017-12-14_09h25_21.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BThe%20code%20that%20I%20have%20so%20far%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheets(3).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22AY2%22).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.FormulaR1C1%20%3D%20%22%3DVLOOKUP(RC%5B-41%5D%2CDennisAR!C%5B-50%5D%2C1%2C0)%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.AutoFill%20Destination%3A%3DRange(%22AY2%3AAY1662%22)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22AY2%3AAY1662%22).Select%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20with%20this%20is%2C%20that%20the%20number%20of%20rows%20with%20data%20always%20change%20every%20week.%20So%20I%20cannot%20use%20a%20static%20row%20number%20of%201662.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20looking%20for%20a%20way%20to%20make%20Destination%3A%3DRange(%22AY%3AAY1662)%20dynamic.%20In%20fact%20it%20has%20to%20refer%20to%20the%20number%20of%20rows%20with%20data%20in%20column%20E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20very%20much%20in%20advance%2C%3C%2FP%3E%0A%3CP%3EKai%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-137261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-523716%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-523716%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Haytham%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394404%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394404%22%20slang%3D%22en-US%22%3EThanks%20Haytham%2C%3CBR%20%2F%3EThis%20worked%20like%20a%20magic%20for%20me%20in%20no%20time.%3CBR%20%2F%3EAppreciate%20your%20knowledge%20and%20interest%20to%20share%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386506%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Haytham%20Amairah%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20facing%20similar%20issues%20and%20chanced%20upon%20this%20thread.%20If%20i%20have%20two%20columns%20(O%20and%20P)%20that%20i%20wish%20to%20autofill%20via%20VBA%2C%20do%20I%20amend%20the%20code%20from%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESelection.AutoFill%20Destination%3A%3DRange(%22O2%3AP313%22)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22O2%3AP313%22).Select%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESelection.AutoFill%20Destination%3A%3DRange(%22O2%3AO%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.AutoFill%20Destination%3A%3DRange(%22P2%3A%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-137337%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-137337%22%20slang%3D%22en-US%22%3E%3CP%3EReplace%20these%20lines%20of%20code%3A%3C%2FP%3E%0A%3CPRE%3ESelection.AutoFill%20Destination%3A%3DRange(%22AY2%3AAY1662%22)%0ARange(%22AY2%3AAY1662%22).Select%3C%2FPRE%3E%0A%3CP%3EWith%20this%3A%3C%2FP%3E%0A%3CPRE%3ESelection.AutoFill%20Destination%3A%3DRange(%22AY2%3AAY%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0ARange(Selection%2C%20Selection.End(xlDown)).Select%0A%3C%2FPRE%3E%0A%3CP%3EGive%20it%20a%20try!%3C%2FP%3E%0A%3CP%3EAnd%20provide%20me%20with%20any%20feedback!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552237%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BDear%20Haytham%2C%20would%20you%20be%20able%20to%20amend%20my%20code%20so%20that%20it%20works%20the%20same%20way%20as%20OP's%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20amend%20the%26nbsp%3B%3CEM%3EActiveCell.End(xlDown)%3C%2FEM%3E%20part%20so%20that%20the%20formula%20(in%20column%20N)%20stops%20at%20the%20last%20row%20that%20contains%20data%20in%20column%20M.%20Thank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.FormulaR1C1%20%3D%20%22%3DIF(RIGHT(RC%5B-8%5D%2C1)%3D%22%22A%22%22%2C%22%22f%22%22%2C%22%22m%22%22)%22%3CBR%20%2F%3EActiveCell.Select%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(ActiveCell%2C%20ActiveCell.End(xlDown))%3CBR%20%2F%3ERange(ActiveCell%2C%20ActiveCell.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(ActiveCell).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EMike%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-557316%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557316%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Mike%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20one%3A%3C%2FP%3E%3CPRE%3ESub%20test()%3CBR%20%2F%3E%3CBR%20%2F%3E%20ActiveCell.FormulaR1C1%20%3D%20%22%3DIF(RIGHT(RC%5B-8%5D%2C1)%3D%22%22A%22%22%2C%22%22f%22%22%2C%22%22m%22%22)%22%3CBR%20%2F%3E%20Selection.AutoFill%20Destination%3A%3DRange(%22N1%3AN%22%20%26amp%3B%20Range(%22M%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3E%20Range(ActiveCell%2C%20ActiveCell.End(xlDown)).Select%3CBR%20%2F%3E%20Selection.Copy%3CBR%20%2F%3E%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%20Application.CutCopyMode%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336966%22%20target%3D%22_blank%22%3E%40W4rcloud%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719784%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719784%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20a%20macro%20to%20unprotect%20sheet%2C%20autofill%20columns%20from%20above%20and%20protect%20sheet%20again.%20I%20am%20using%20below%20formula%20however%20it%20is%20giving%20error.%20Can%20anyone%20help%20please%3F%3C%2FP%3E%3CP%3ESub%20sbUnProtectSheet()%3C%2FP%3E%3CP%3EActiveSheet.Unprotect%20%229999%22%3C%2FP%3E%3CP%3ESet%20SourceRange%20%3D%20ActiveSheet.Range(%22B10%3AH10%22)%3CBR%20%2F%3ESet%20fillRange%20%3D%20ActiveSheet.Range(%22B11%3AH39%22)%3CBR%20%2F%3ESourceRange.AutoFill%20Destination%3A%3DfillRange%3CBR%20%2F%3EActiveSheet.Protect%20%229999%22%2C%20True%2C%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EAnil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719945%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366287%22%20target%3D%22_blank%22%3E%40AnilChhabra%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20better%20to%20post%20your%20question%20as%20a%20new%20conversation%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3Ein%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Ecommunity.%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20can%2C%20provide%20us%20with%20a%20sample%20of%20the%20worksheet%20you're%20working%20on!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3ERegards%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-747928%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20really%20hoping%20you%20can%20point%20me%20in%20the%20right%20direction%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20do%20the%20same%20thing%20as%20OP%2C%20however%20I%20have%20blank%20rows%20above%20and%20to%20the%20left%20of%20my%20data%20and%20not%20sure%20if%20that%20is%20throwing%20it%20off.%20Below%20is%20a%20screenshot%20(had%20to%20switch%20all%20values%20to%20xxxx%20for%20privacy%20purposes)%2C%20I%20want%20my%20Macro%20to%20create%20a%20new%20Column%20to%20the%20left%20of%20Column%20J%20in%20the%20screenshot%2C%20where%20a%20new%20Column%20J%20will%20be%20created%20that%20will%20house%20my%20formula%20beginning%20in%20the%20new%20Cell%20J6.%20I%20then%20want%20the%20Macro%20to%20extend%20this%20formula%20down%20the%20last%20row%20in%20any%20of%20the%20columns%20to%20the%20left%20of%20Column%20J%2C%20as%20they%20will%20all%20always%20be%20populated%2C%20however%20I%20have%20been%20tying%20everything%20to%20Column%20I%20in%20my%20troubleshooting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122947iE82599575324F096%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Macro%20Help.PNG%22%20title%3D%22Macro%20Help.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20snip%20of%20code%20that%20I%20know%20is%20causing%20the%20issue%2C%20with%20the%20problem%20rows%20likely%20being%20the%20three%20rows%20below%20my%20IF%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheets(%22Raw_Data%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%208).Columns(%22A%3AA%22).EntireColumn.Select%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlToRight%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3EActiveCell.Offset(4%2C%200).Range(%22A1%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22MEASURE%20OUTCOME%22%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%22%3DIF(AND(RC%5B-3%5D%3D%22%22MET%22%22%2CRC%5B-2%5D%3D%22%22MET%22%22%2CRC%5B-1%5D%3D%22%22MET%22%22)%2C%22%22Met%22%22%2C%22%22Not%20Met%22%22)%22%3CBR%20%2F%3EActiveCell.Select%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DActiveCell.Range(%22A1%3AA366%22)%3CBR%20%2F%3EActiveCell.Range(%22A1%3AA366%22).Select%3CBR%20%2F%3ESelection.FormatConditions.Add%20Type%3A%3DxlTextString%2C%20String%3A%3D%22Met%22%2C%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20able%20to%20help%20me%20re-write%20this%20code%20to%20have%20the%20Macro%20fill%20the%20IF%20formula%20down%20to%20the%20last%20row%20of%20data%20rather%20than%20just%20the%20last%20cell%20in%20my%20current%20selection%3F%20The%20number%20of%20rows%20will%20change%20on%20a%20daily%20basis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-747992%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F102679%22%20target%3D%22_blank%22%3E%40Kai%20El%20Harrar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20you%20wee%20to%20create%20a%20Table%20instead%2C%20Insert%20%26gt%3B%20Table%2C%20then%20your%20formula%20would%20automatically%20be%20inserted%20as%20you%20add%20rows%20to%20the%20Table%2C%20without%20the%20need%20for%20any%20VBA%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750703%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374244%22%20target%3D%22_blank%22%3E%40Steven1835%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20about%20the%20late%20reply!%3C%2FP%3E%3CP%3EI%20think%20you%20need%20this%20one%3A%3C%2FP%3E%3CPRE%3ESub%20FillFormulaDown()%3CBR%20%2F%3E'To%20check%20if%20the%20column%20is%20already%20exist%3CBR%20%2F%3E%20%20%20If%20Application.WorksheetFunction.IsFormula(Range(%22J5%22).Offset(1%2C%200))%20Then%3CBR%20%2F%3E%20%20%20%20%20'skip%3CBR%20%2F%3E%20%20%20Else%3CBR%20%2F%3E%20%20%20%20%20Columns(%22J%3AJ%22).Insert%20Shift%3A%3DxlToRight%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3E%20%20%20%20%20Range(%22J5%22).Value%20%3D%20%22MEASURE%20OUTCOME%22%3CBR%20%2F%3E%20%20%20End%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20%20Range(%22J5%22).Offset(1%2C%200).Activate%3CBR%20%2F%3E%20%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22%3DIF(AND(RC%5B-3%5D%3D%22%22MET%22%22%2CRC%5B-2%5D%3D%22%22MET%22%22%2CRC%5B-1%5D%3D%22%22MET%22%22)%2C%22%22Met%22%22%2C%22%22Not%20Met%22%22)%22%3CBR%20%2F%3E%20%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22J6%3AJ%22%20%26amp%3B%20Range(%22I%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3E%20%20%20%20%20ActiveCell.EntireColumn.AutoFit%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20find%20it%20in%20the%20attached%20workbook.%3C%2FP%3E%3CP%3EPlease%20test%20it%20and%20tell%20me%20what%20you%20thought.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755972%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20your%20code%20in%20my%20macro%20when%20it%20imports%20from%20a%20text%20file%20and%20reorganizes%20the%20records.%20As%20part%20of%20it%2C%20it%20is%20supposed%20to%20copy%20down%20rows%20of%20a%20table%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.FormulaR1C1%20%3D%20%22%3DR%5B-1%5DC%22%3CBR%20%2F%3ERange(%22A3%22).Select%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22A3%3AA%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ERange(%22B3%22).Select%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22B3%3AB%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20nicely%20but%20if%20there%20is%20a%20single%20line%20of%20record%20in%20the%20file%2C%20it%20copies%20the%20row%20but%20then%20it%20adds%20a%20second%20row%2C%20copies%20down%20the%20first%20row%20in%20column%20A%20and%20B%20then%20returns%20a%20VB%20Error%20as%20shown%20in%20the%20attached%20screenshot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20how%20to%20deal%20with%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759992%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376774%22%20target%3D%22_blank%22%3E%40NickNick33%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20figure%20out%20the%20problem%2C%20please%20provide%20a%20sample%20of%20the%20data%20you%20work%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-760000%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-760000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESure%2C%20it%20is%20attached.%20Th%20eoriginal%20file%20is%20a%20tsv%20file%20but%20the%20upload%20tool%20would%20not%20accept%20tsv%20so%20I%20had%20to%20change%20it%20to%20csv.%20You%20will%20need%20to%20change%20it%20to%20tsv%20for%20the%20Excel%20file%20to%20read%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763166%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376774%22%20target%3D%22_blank%22%3E%40NickNick33%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20got%20after%20opening%20your%20file%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123680iDEE2BAA5135A6A8B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_1.png%22%20title%3D%22Screenshot_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20but%20the%20data%20isn't%20clear%20and%20I'm%20not%20sure%20what%20you're%20trying%20to%20do!%3C%2FP%3E%3CP%3EBased%20on%20your%20macro%2C%20you%20want%20to%20fill%20down%20Column%20A%20%26amp%3B%20B%20based%20on%20the%20number%20of%20rows%20in%20column%20E%20%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763248%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20the%20macro%26nbsp%3Bsteps%3A%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3EImports%20the%20source%20file%20I%20provided%2C%20%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Eadds%202%20columns%20(Shipmentid%20and%20Name)%20in%20addition%20to%20the%20columns%20in%20the%20source%20file.%20%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIt%20takes%20what%20is%20in%20B1%20in%20the%20source%20as%20Shipmentid%20and%20copies%20down%20the%20new%20column%20A%20%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIt%20takes%20what%20is%20in%20B2%2C%20copies%20down%20the%20new%20column%20B%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EDeletes%20the%20top%20rows%20up%20to%20the%20headings%20so%20that%20the%20whole%20file%20becomes%20one%20uniform%20table.%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThis%20works%20fine%20except%20when%20there%20is%20a%20single%20in%20the%20source.%20I%20have%20attached%20both%20the%20Excel%20file%20and%20the%20different%20files%20that%20are%20used%20as%20the%20source.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20is%20created%20to%20import%20the%20file%20called%20current.tsv.%20We%20just%20swap%20the%20files%20we%20want%20to%20import%20by%20renaming%20them%20%22current.tsv%22.%20So%20I%20provided%20one%20sith%20single%20line%20and%20another%20with%20multiple.%20The%20one%20with%20multiple%20works%20fine%20but%20single%20one%20returns%20error%20as%20the%20VBA%20seems%20to%20have%20a%20problem%20with%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurious%20situation.%20I%20hope%20this%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-764701%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-764701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376774%22%20target%3D%22_blank%22%3E%40NickNick33%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20I%20reproduced%20this%20issue%2C%20I%20noticed%20that%20the%20error%20also%20occurs%20even%20if%20you%20have%20two%20records%20in%20the%20imported%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20one%20or%20two%20rows%20in%20the%20imported%20file%2C%20there%20is%20no%20need%20to%20use%20the%20AutoFill%20method%20in%20the%20macro%20because%20all%20rows%20in%20the%20table%20are%20already%20filled.%3C%2FP%3E%3CP%3EIf%20you%20use%20it%20in%20this%20case%2C%20there%20will%20be%20an%20error!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20is%20to%20make%20the%20code%20smart%20somehow%20to%20see%20if%20the%20imported%20file%20has%20less%20than%20three%20rows%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3Eand%20if%20so%2C%20skip%20the%20autofill%20process%20to%20prevent%20the%20error.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20suggest%20to%20overcome%20this%20issue%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESub%20PrepFile()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E'%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E'%20PrepFile%20Macro%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E'%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E'%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESheets(%22Sheet1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EWith%20ActiveSheet.ListObjects.Add(SourceType%3A%3D0%2C%20Source%3A%3D%20_%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22OLEDB%3BProvider%3DMicrosoft.Mashup.OleDb.1%3BData%20Source%3D%24Workbook%24%3BLocation%3Dcurrent%3BExtended%20Properties%3D%22%22%22%22%22%20_%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%2C%20Destination%3A%3DRange(%22%24A%241%22)).QueryTable%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.CommandType%20%3D%20xlCmdSql%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.CommandText%20%3D%20Array(%22SELECT%20*%20FROM%20%5Bcurrent%5D%22)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.RowNumbers%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.FillAdjacentFormulas%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.PreserveFormatting%20%3D%20True%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.RefreshOnFileOpen%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.BackgroundQuery%20%3D%20True%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.RefreshStyle%20%3D%20xlInsertDeleteCells%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.SavePassword%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.SaveData%20%3D%20True%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.AdjustColumnWidth%20%3D%20True%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.RefreshPeriod%20%3D%200%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.PreserveColumnInfo%20%3D%20True%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.ListObject.DisplayName%20%3D%20%22current%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E.Refresh%20BackgroundQuery%3A%3DFalse%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EEnd%20With%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22K10%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22Shipment%20ID%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22L10%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22Shipment%20Name%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22B2%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Copy%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22K11%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22B3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Copy%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22L11%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERows(%2210%3A10%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Copy%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERows(%221%3A1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERows(%222%3A10%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A10%22).Activate%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Delete%20Shift%3A%3DxlUp%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EColumns(%22K%3AL%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Cut%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EColumns(%22A%3AA%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Insert%20Shift%3A%3DxlToRight%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EIf%20Application.WorksheetFunction.CountA(Range(%22E%3AE%22))%20-%201%20%3D%201%20Then%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EExit%20Sub%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EElseIf%20Application.WorksheetFunction.CountA(Range(%22E%3AE%22))%20-%201%20%3D%202%20Then%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22%3DR%5B-1%5DC%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22B3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22%3DR%5B-1%5DC%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EExit%20Sub%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EEnd%20If%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22%3DR%5B-1%5DC%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22B3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EApplication.CutCopyMode%20%3D%20False%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EActiveCell.FormulaR1C1%20%3D%20%22%3DR%5B-1%5DC%22%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.AutoFill%20Destination%3A%3DRange(%22A3%3AA%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22B3%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.AutoFill%20Destination%3A%3DRange(%22B3%3AB%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22A1%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EEnd%20Sub%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20replace%20your%20old%20macro%20with%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808061%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20I%20have%20to%20add%20if%20the%20source%20isn't%20in%20the%20same%20worksheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808084%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393444%22%20target%3D%22_blank%22%3E%40To31416%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EWhat's%20the%20source%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EPlease%20explain%20the%20problem%20well%3C%2FSPAN%3E%3C%2FSPAN%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808110%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20reply!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20file%20in%20which%20I%20import%20various%20reports%20and%20in%20this%20file%20I%20have%20a%20sheet%2C%20where%20I%20use%20those%20data%20for%20my%20formulas.%3C%2FP%3E%3CP%3EThe%20various%20reports%20I%20import%20have%20the%20same%20length%20but%20differ%20in%20length%20every%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20main%20sheet%20where%20I%20use%20the%20imported%20data%20the%20first%20row%20always%20stays%20the%20same%20with%20all%20kind%20of%20formulas.%20However%2C%20I%20want%20my%20Macro%20to%20autofill%20the%20other%20rows%20until%20there%20is%20no%20more%20data%20in%20one%20of%20the%20imported%20reports.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20now%20I%20have%20to%20set%20the%20autofill%20range%20manually.%20So%20if%20the%20report%20is%20too%20long%2C%20it's%20either%20cut%20or%20if%20it's%20too%20short%20I%20get%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20was%20understandable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808326%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393444%22%20target%3D%22_blank%22%3E%40To31416%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20this%20situation%20is%20different!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EBut%20I%20think%20you%20need%20to%20somehow%20get%20the%20total%20number%20of%20rows%20in%20the%20imported%20reports%20and%20then%20use%20that%20number%20in%20the%20macro.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20can%20attach%20your%20file%20or%20a%20sample%20of%20it%2C%20this%20is%20would%20be%20helpful!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808561%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20helping%20me%20out%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20removed%20all%20data%20and%20a%20settings%20sheet%20but%20the%20important%20data%20is%20still%20there.%3C%2FP%3E%3CP%3EThe%20macro-button%20is%20in%20the%20sheet%20%22Output%22%20Cell%20%22F1%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20length%20should%20be%20the%20same%20as%20I%20got%20days%20in%20the%20reports.%20The%20totals%20column%20excluded.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808673%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393444%22%20target%3D%22_blank%22%3E%40To31416%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20updated%20the%20code%20behind%20the%20%3CSTRONG%3ELoad%3C%2FSTRONG%3Ebutton%20to%20fill%20down%20the%20rows%20based%20on%20the%20number%20of%20rows%20in%20(yield)%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20FillRowsDown()%0A%20%20%20%20'Get%20the%20last%20row%20in%20sheet%20(yield)%20and%20save%20it%20in%20the%20lastRow%20variable%0A%20%20%20%20Dim%20lastRow%20As%20Long%0A%20%20%20%20lastRow%20%3D%20Sheets(%22yield%22).Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%0A%20%20%20%20'Subtract%20the%20first%206%20rows%20from%20the%20lastRow%0A%20%20%20%20lastRow%20%3D%20lastRow%20-%206%0A%20%20%20%20%0A%20%20%20%20'Subtract%20the%20Total%20row%20(if%20exists)%0A%20%20%20%20If%20LCase(Sheets(%22yield%22).Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Value)%20%3D%20%22total%22%20Then%0A%20%20%20%20%20%20%20%20lastRow%20%3D%20lastRow%20-%201%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20With%20Sheets(%22output%22)%0A%20%20%20%20%20.Range(%22A4%3AU4%22).AutoFill%20Destination%3A%3DRange(%22A4%3AU%22%20%26amp%3B%20lastRow%20%2B%203)%0A%20%20%20%20%20.Range(%22D4%22).Select%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808687%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20so%20much!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20really%20were%20of%20great%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819678%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20%3CA%20target%3D%22_blank%22%3EHaytham%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20use%20to%20populate%20Row%20H%20to%20length%20of%20Row%20E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESub%20Test()%0A%0A%20%20%20Range(%22H2%22).Select%0A%20%20%20%20%0A%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22H2%3AH%22%20%26amp%3B%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0ARange(Selection%2C%20Selection.End(xlDown)).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20I%20need%20to%20add%2Fchange%20to%20get%20content%20from%20single%20Cell%20A2%20and%20fill%20down%20from%20next%20empty%20in%20H%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(the%20content%20of%20A2%20%26nbsp%3Bchanges%20and%20would%20like%20to%20see%20all%20changes%20updated%20in%20H%20rather%20than%20just%20overwriting)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819903%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397283%22%20target%3D%22_blank%22%3E%40aimeelou%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20you%20well%2C%20nothing%20has%20to%20change%20in%20the%20code!%3C%2FP%3E%3CP%3EYou%20need%20to%20use%20the%20below%20formula%20in%20cell%20H2%20to%20link%20this%20cell%20to%20cell%20A2.%3C%2FP%3E%3CPRE%3E%3DA2%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20then%2C%20run%20the%20code%20so%20that%20you%20fill%20this%20formula%20down%20until%20the%20last%20row%20in%20column%20E%20and%20make%20the%20column%20H%20and%20A%20linked%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821268%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20Haytham%2C%3C%2FP%3E%3CP%3Eunfortunately%20I%20am%20probably%20doing%20something%20wrong%20as%20when%20I%20link%20the%20Cells%20then%20run%20macro%20rather%20than%3C%2FP%3E%3CP%3Efilling%20down%20with%20the%20text%20that%20is%20in%20A2%20-%20cells%20in%20H%20fill%20down%20with%20a%20zero%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821405%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397283%22%20target%3D%22_blank%22%3E%40aimeelou%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20attach%20a%20sample%20of%20the%20data%20you%20working%20on%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822015%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397283%22%20target%3D%22_blank%22%3E%40aimeelou%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20to%20fill%20the%20column%20H%20with%20the%20content%20of%20only%20cell%20A2%20so%20that%20each%20cell%20in%20column%20H%20will%20be%20filled%20with%20the%20same%20text%20in%20cell%20A2.%3C%2FP%3E%3CP%3EThen%20you%20just%20need%20to%20lock%20the%20formula%20in%20cell%20H2%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3D%24A%242%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821965%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20helping%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830084%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Amairah%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20question%20somewhat%20similar%20to%20the%20post%20above.%3C%2FP%3E%3CP%3ECurrently%2C%20I%20want%20to%20create%20a%20column%20with%20a%20function%20and%20want%20to%20autofill%20the%20column%20with%20the%20function%20until%20the%20end%20of%20the%20row%3C%2FP%3E%3CP%3EHowever%2C%20I%20wish%20to%20put%20in%20the%20number%20to%20rows%20so%20that%20it%20works%20on%20any%20worksheet%20(maybe%20with%20different%20number%20of%20rows)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScreenshot%20below%20is%20the%20formula%20I%20want%20to%20put%20into%20the%20column%20and%20autofill%20until%20the%20end%20of%20the%20row%20for%20any%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129237i9420396891A65B30%2Fimage-dimensions%2F567x244%3Fv%3D1.0%22%20width%3D%22567%22%20height%3D%22244%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAlso%2C%20this%20is%20what%20I%20have%20so%20far%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20MacroCode_Column%20fill%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20myFirstColumn%20As%20Integer%3CBR%20%2F%3EDim%20myLastColumn%20As%20Integer%3CBR%20%2F%3EDim%20myFirstFieldRow%20As%20Integer%3CBR%20%2F%3EDim%20myFirstDataRow%20As%20Integer%3CBR%20%2F%3EDim%20myLastDataRow%20As%20Integer%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EmyFirstFieldRow%20%3D%205%3CBR%20%2F%3EmyFirstDataColumn%20%3D%20Cells(5%2C%20Columns.Count).End(xlToLeft).Column%20%2B%201%3CBR%20%2F%3EmyLastDataRow%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Multiple%20Occurred%3C%2FP%3E%3CP%3EActiveSheet.Cells(myFirstFieldRow%2C%20myFirstDataColumn%20%2B%206).Select%3CBR%20%2F%3EActiveCell.Formula%20%3D%20%22MCO%20Incurred%22%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22%3DSUMIF(R6C5%3AR1321C5%2CRC5%2CR6C22%3AR1321C22)*RC31%22%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(Cells(myFirstFieldRow%20%2B%201%2C%20myFirstDataColumn%20%2B%206)%2C%20Cells(myLastDataRow%2C%20myFirstDataColumn%20%2B%206))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831415%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400645%22%20target%3D%22_blank%22%3E%40D_Jang3884%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20what%20you%20want%20to%20fill%20the%20formula%20down%3F%3C%2FP%3E%3CP%3EYou%20see%20that%20the%20solutions%20here%20in%20this%20conversation%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Efocus%20on%20filling%20the%20rows%20in%20a%20column%20based%20on%20the%20number%20of%20rows%20of%20another%20column.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831440%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Amairah%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20if%20I%20asked%20a%20non-related%20question%20to%20the%20post%20but%20regarding%20to%20your%20question%20-%20basically%20I%20want%20to%20update%20the%20number%20of%20rows%20to%20the%20formula%20below%20using%20the%20following%20variables%20(which%20I%20created%20to%20set%20first%20row%20to%20be%205%20and%20the%20last%20row%20to%20be%20determined%20based%20on%20data%20I'm%20using)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20myFirstDataRow%20As%20Integer%3CBR%20%2F%3EDim%20myLastDataRow%20As%20Integer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EmyFirstFieldRow%20%3D%205%3C%2FP%3E%3CP%3EmyLastDataRow%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20the%20formula%20I%20want%20to%20update%20is%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.FormulaR1C1%20%3D%20%22%3DSUMIF(R6C5%3AR1321C5%2CRC5%2CR6C22%3AR1321C22)*RC31%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20the%20rows%20in%20the%20formula%20is%20in%20numbers%20but%20I%20wish%20to%20replace%20it%20using%20dimensions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20greatly%20appreciate%20it%20if%20you%20could%20help%20or%20direct%20me%20to%20where%20I%20can%20ask.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831729%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400645%22%20target%3D%22_blank%22%3E%40D_Jang3884%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20post%20this%20question%20in%20a%20separate%20post%20by%20starting%20a%20new%20conversation%20in%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_self%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3CP%3EIf%20you%20didn't%20find%20the%20answer%2C%20you%20may%20ask%20this%20%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2Ftagged%2Fvba%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ecommunity%3C%2FA%3Einstead%20under%20%5Bvba%5D%20tag.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857549%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857549%22%20slang%3D%22en-US%22%3EHi%20%40Haytham%2C%20I'm%20trying%20to%20do%20the%20same%20as%20above%20to%20autofill%20multiple%20columns%20to%20varying%20rows%20and%20I%20have%20tried%20a%20couple%20of%20different%20things%20I've%20seen%20above%2C%20but%20I%20fear%20I%20don't%20have%20the%20excel%20coding%20knowledge%20to%20know%20what%20I'm%20doing%20wrong.%20the%20following%20is%20my%20macro%20and%20I'd%20like%20the%20%22E145040%22%20to%20be%20variable.%20I%20also%20dont%20know%20if%20I%20need%20to%20change%20the%20%22%24A%24915118%22%2C%20%22%24C%24915118%22%2C%20%22%24E%24915118%22%2C%20%22%24G%24915118%22%20and%20%22%24AC%24145040%22%20as%20well.%20Thank%20you%20so%20much%20in%20advance!%20Sub%20Macro5()%20'%20'%20Macro5%20Macro%20'%20'%20Range(%22Y1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20%22COMP%22%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20_%20%22%3DIF(RC%5B-21%5D%3D%22%22Closed%22%22%2C1%2CIF(RC%5B-21%5D%3D%22%22Cancelled%22%22%2C1%2C0))%22%20ActiveCell.Offset(-1%2C%201).Range(%22A1%22).Select%20ActiveCell.Select%20ActiveCell.FormulaR1C1%20%3D%20%22PROC%22%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20_%20%22%3DIF(OR(RC%5B-22%5D%3D%22%22Closed%22%22%2CRC%5B-22%5D%3D%22%22Verified%22%22%2CRC%5B-22%5D%3D%22%22Ready_To_Verify%22%22%2CRC%5B-22%5D%3D%22%22Cancelled%22%22)%2C1%2CIF(OR(RC%5B-22%5D%3D%22%22Assigned%22%22%2CRC%5B-22%5D%3D%22%22Reassigned%22%22%2CRC%5B-22%5D%3D%22%22Pending%22%22)%2CIF(COUNTIF(RC%5B-11%5D%3ARC%5B-8%5D%2C%22%22Yes%2FOui%22%22)%26gt%3B0%2C1%2C0)%2C0))%22%20ActiveCell.Offset(-1%2C%201).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20%22PC%22%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20_%20%22%3DIF(OR(RC%5B-23%5D%3D%22%22Reassigned%22%22%2CRC%5B-23%5D%3D%22%22Assigned%22%22%2CRC%5B-23%5D%3D%22%22Pending%22%22)%2CIF(COUNTIF(RC%5B-12%5D%3ARC%5B-9%5D%2C%22%22Yes%2FOui%22%22)%26gt%3B0%2C1%2C0)%2C0)%22%20ActiveCell.Offset(-1%2C%201).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20%22VER%22%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20_%20%22%3DIF(RC%5B-24%5D%3D%22%22Verified%22%22%2C1%2CIF(RC%5B-24%5D%3D%22%22Ready_To_Verify%22%22%2C1%2C0))%22%20ActiveCell.Offset(-1%2C%201).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20%22UNPROC%22%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%20ActiveCell.FormulaR1C1%20%3D%20_%20%22%3DIF(OR(RC%5B-25%5D%3D%22%22Closed%22%22%2CRC%5B-25%5D%3D%22%22Verified%22%22%2CRC%5B-25%5D%3D%22%22Ready_To_Verify%22%22%2CRC%5B-25%5D%3D%22%22Cancelled%22%22)%2C0%2CIF(OR(RC%5B-25%5D%3D%22%22Assigned%22%22%2CRC%5B-25%5D%3D%22%22Pending%22%22)%2CIF(COUNTIF(RC%5B-14%5D%3ARC%5B-11%5D%2C%22%22Yes%2FOui%22%22)%26gt%3B0%2C0%2C1)%2C1))%22%20ActiveCell.Offset(0%2C%20-4).Range(%22A1%3AE1%22).Select%20Selection.AutoFill%20Destination%3A%3DActiveCell.Range(%22A1%3AE145040%22)%20ActiveCell.Range(%22A1%3AE145040%22).Select%20Sheets.Add%20After%3A%3DActiveSheet%20Sheets(%22Sheet1%22).Select%20Rows(%221%3A1%22).Select%20Selection.AutoFilter%20ActiveSheet.Range(%22%24A%241%3A%24AC%24145040%22).AutoFilter%20Field%3A%3D25%2C%20Criteria1%3A%3D%220%22%20Columns(%22J%3AJ%22).Select%20Selection.Copy%20Sheets(%22Sheet2%22).Select%20Range(%22A1%22).Select%20ActiveSheet.Paste%20Sheets(%22Sheet1%22).Select%20Application.CutCopyMode%20%3D%20False%20ActiveSheet.ShowAllData%20Range(%22Z2%22).Select%20ActiveSheet.Range(%22%24A%241%3A%24AC%24145040%22).AutoFilter%20Field%3A%3D26%2C%20Criteria1%3A%3D%220%22%20Columns(%22J%3AJ%22).Select%20Selection.Copy%20Sheets(%22Sheet2%22).Select%20Range(%22C1%22).Select%20ActiveSheet.Paste%20Sheets(%22Sheet1%22).Select%20Application.CutCopyMode%20%3D%20False%20ActiveSheet.ShowAllData%20Range(%22M2%22).Select%20ActiveSheet.Range(%22%24A%241%3A%24AC%24145040%22).AutoFilter%20Field%3A%3D27%2C%20Criteria1%3A%3D%221%22%20Columns(%22J%3AJ%22).Select%20Selection.Copy%20Sheets(%22Sheet2%22).Select%20Range(%22E1%22).Select%20ActiveSheet.Paste%20Sheets(%22Sheet1%22).Select%20Application.CutCopyMode%20%3D%20False%20ActiveSheet.ShowAllData%20Range(%22M2%22).Select%20ActiveSheet.Range(%22%24A%241%3A%24AC%24145040%22).AutoFilter%20Field%3A%3D28%2C%20Criteria1%3A%3D%221%22%20Columns(%22J%3AJ%22).Select%20Selection.Copy%20Sheets(%22Sheet2%22).Select%20Range(%22G1%22).Select%20ActiveSheet.Paste%20Columns(%22A%3AA%22).Select%20Application.CutCopyMode%20%3D%20False%20ActiveSheet.Range(%22%24A%241%3A%24A%24915118%22).RemoveDuplicates%20Columns%3A%3D1%2C%20Header%3A%3D%20_%20xlNo%20Columns(%22C%3AC%22).Select%20ActiveSheet.Range(%22%24C%241%3A%24C%24915118%22).RemoveDuplicates%20Columns%3A%3D1%2C%20Header%3A%3D%20_%20xlNo%20Columns(%22E%3AE%22).Select%20ActiveSheet.Range(%22%24E%241%3A%24E%24915118%22).RemoveDuplicates%20Columns%3A%3D1%2C%20Header%3A%3D%20_%20xlNo%20Columns(%22G%3AG%22).Select%20ActiveSheet.Range(%22%24G%241%3A%24G%24915118%22).RemoveDuplicates%20Columns%3A%3D1%2C%20Header%3A%3D%20_%20xlNo%20Sheets(%22Sheet1%22).Select%20ActiveSheet.ShowAllData%20Sheets.Add%20After%3A%3DActiveSheet%20Sheets(%22Sheet1%22).Select%20ActiveSheet.Range(%22%24A%241%3A%24AC%24145040%22).AutoFilter%20Field%3A%3D9%2C%20Criteria1%3A%3D%20_%20%22PA%20Exceptions%20%E2%80%93%20Exception%20PA%22%20Cells.Select%20Range(%22J1%22).Activate%20Selection.Copy%20Sheets(%22Sheet3%22).Select%20Range(%22A1%22).Select%20ActiveSheet.Paste%20Application.CutCopyMode%20%3D%20False%20Sheets.Add%20ActiveWorkbook.PivotCaches.Create(SourceType%3A%3DxlDatabase%2C%20SourceData%3A%3D%20_%20%22Sheet3!R1C1%3AR981068C29%22%2C%20Version%3A%3DxlPivotTableVersion15).CreatePivotTable%20_%20TableDestination%3A%3D%22Sheet4!R3C1%22%2C%20TableName%3A%3D%22PivotTable3%22%2C%20DefaultVersion%20_%20%3A%3DxlPivotTableVersion15%20Sheets(%22Sheet4%22).Select%20Cells(3%2C%201).Select%20With%20ActiveSheet.PivotTables(%22PivotTable3%22).PivotFields(%22User%20ID%22)%20.Orientation%20%3D%20xlRowField%20.Position%20%3D%201%20End%20With%20ActiveSheet.PivotTables(%22PivotTable3%22).AddDataField%20ActiveSheet.PivotTables(%20_%20%22PivotTable3%22).PivotFields(%22PROC%22)%2C%20%22Sum%20of%20PROC%22%2C%20xlSum%20ActiveWindow.SmallScroll%20Down%3A%3D-84%20Sheets(%22Sheet1%22).Select%20ActiveSheet.ShowAllData%20Sheets(%22Sheet1%22).Select%20Sheets(%22Sheet1%22).Name%20%3D%20%221.0%20PA%20RAW%22%20Sheets(%22Sheet4%22).Select%20Sheets(%22Sheet4%22).Name%20%3D%20%222.1%20production%22%20Sheets(%22Sheet3%22).Select%20Sheets(%22Sheet3%22).Name%20%3D%20%222.0%20PA%20exceptions%22%20Sheets(%22Sheet2%22).Select%20Sheets(%22Sheet2%22).Name%20%3D%20%221.1%20Daily%20update%22%20End%20Sub%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895072%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20your%20suggestion%20and%20it%20works%20fine%2C%20but%20not%20for%20cases%20where%20there%20is%20only%201%20row%20of%20data%20or%20no%20data%20(only%20heading).%20I%20have%20a%20heading%20in%20row%201%2C%20formula%20in%20row%202%20(cell%20E2)%20which%20I'm%20trying%20to%20copy%20to%20the%20end%20of%20the%20table%20using%20this%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelection.AutoFill%20Destination%3A%3DRange(%22E2%3AE%22%20%26amp%3B%20Range(%22C%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20your%20advice.%3C%2FP%3E%3CP%3ESarka%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907723%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420655%22%20target%3D%22_blank%22%3E%40jahoda%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20late%20reply!%3C%2FP%3E%3CP%3EPlease%20attach%20a%20sample%20of%20the%20data%20you're%20working%20to%20fit%20the%20code%20on%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016328%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Haytham%3C%2FP%3E%3CP%3EI%20am%20with%20difficult%20to%20make%20Autofill%20in%202%20different%20columns%3C%2FP%3E%3CP%3EI%20recorded%20the%20macro%20and%20made%20change%20you%20indicated%20before.%3C%2FP%3E%3CP%3EIt%20actually%20works%20but%2C%20only%20for%20the%20second%20column%2C%20the%20first%20is%20not%20filled.%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20understand%20what%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20Measure_Key()%0A'Measure_Key%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%3DIF(RC%5B3%5D%26lt%3B%26gt%3B%22%22%22%22%2CRC%5B2%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B3%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B4%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B5%5D%2C%22%22%22%22)%22%0A%20%20%20%20Range(%22A2%22).Select%0A%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22A2%3AA%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%20%20%20%20Range(%22I2%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%3DIF(RC%5B2%5D%26lt%3B%26gt%3B%22%22%22%22%2CRC%5B1%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B2%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B3%5D%26amp%3B%22%22_%22%22%26amp%3BRC%5B4%5D%2C%22%22%22%22)%22%0A%20%20%20%20Range(%22I2%22).Select%0A%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22I2%3AI%22%20%26amp%3B%20Range(%22J%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1028886%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1028886%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haitham%3C%2FP%3E%3CP%3EI%20am%20new%20to%20VBA%20and%20I%20am%20trying%20to%20amend%20the%26nbsp%3B%3CEM%3EActiveCell.End(xlDown)%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Epart%20so%20that%20the%20formula%20(in%20column%20A)%20stops%20at%20the%20last%20row%20that%20contains%20data%20in%20column%20A.%20and%20again%20starts%20to%20autofill%20from%20this%20column%20to%20the%20next%20contains%20data%20and%20goes%20on%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20thank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3Bthis%20is%20a%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158625i7C9BA1739B891378%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20autofill()%3C%2FP%3E%3CP%3EDim%20Wad%20As%20String%3CBR%20%2F%3EWad%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%3CBR%20%2F%3ERange(%22A30%22).Select%3CBR%20%2F%3ESelection.autofill%20Destination%3A%3DRange(%22A30%3AA%22%20%26amp%3B%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlDown).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3C%2FP%3E%3CP%3ESelection.autofill%20Destination%3A%3DRange(%22wad%3AA%22%20%26amp%3B%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlDown).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWaddah%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1034059%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1034059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F467134%22%20target%3D%22_blank%22%3E%40walshamasi%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Waddah%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20late%20reply%2C%20but%20I%20need%20some%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eclarifications%20as%20column%20A%20seems%20to%20have%20no%20formulas%20in%20it!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20can%20attach%20the%20worksheet%20here%2C%20that%20would%20be%20better.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ERegards%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1097145%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F508855%22%20target%3D%22_blank%22%3E%40Scottmonk%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20one%20instead%3A%3C%2FP%3E%3CPRE%3ERange(%22C5%22).AutoFill%20Destination%3A%3DRange(%22C5%3AC%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(%22L5%22).AutoFill%20Destination%3A%3DRange(%22L5%3AL%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094064%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094064%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EPlease%20can%20you%20help%2C%20I%E2%80%99m%20trying%20to%20run%20a%20macro%20so%20it%20drags%20down%20the%20formula%20to%20the%20last%20row%20and%20it%20keeps%20coming%20up%20as%20error%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22C5%2CL5%22).Select%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22C5%3AC%22%20%26amp%3Bamp%3B%20Range(%22B%22%20%26amp%3Bamp%3B%20Rows.count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098297%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098297%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20this%20worked%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322448%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20code%20is%20erroring%20out%20at%20the%20last%20statement.%20any%20ideas%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22%3DIF('SD_L1%20Calls%20by%20Inquiry%20Type'!RC%5B-1%5D%3DRC%5B-1%5D%2C%22%22Match%22%22%2C%22%22Possible%20cell%20shift%20or%20new%20department%20added%22%22)%22%3CBR%20%2F%3EActiveSheet.Range(%22B4%22).Select%3C%2FP%3E%3CP%3ESelection.AutoFill%20Destination%3A%3DRange(%22B4%3AB255%22)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325884%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325884%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20the%20following%20VBA%20copying%20down%20the%20%3Dleft%20formula%20in%20G%20down%20to%20the%20last%20row%20in%20column%20F.%20But%20now%20I%20want%20column%20F%20to%20be%20A%20on%20Sheet%3D%22Step%202%22%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Copy%20above%20formula%20to%20last%20row%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22G3%3AA%22%20%26amp%3B%20Range(%22F%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20thing%20is%20that%20I%20now%20want%20the%20colum%20that%20was%20%22F%22%20to%20now%20be%20%22A%22%20on%20sheet%20%22Step%202%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20I%20update%20the%20above%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApreciate%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDuan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1326159%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326159%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3EI've%20test%20your%20Solution%20in%20two%20WorkBook%20(A%20Test%20and%20Original)%20but%20it%20only%20work%20in%20the%20TestBook%20(it%20don't%20use%20a%20%22Fixed%20Column%22%20%5BCopy-Pasted%20in%20Other%20Column%20and%20Deleted%20Duplicates%20Values%5D).%3C%2FP%3E%3CP%3EI'll%20Post%20a%20CodeExample%20and%20MyProblem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*Code%20Example%3A%3C%2FP%3E%3CP%3ERange(%22N11%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.FormulaR1C1%20%3D%20%22%3DVLOOKUP(RC%5B-1%5D%2CC%5B-13%5D%3AC%5B-5%5D%2C3%2CFALSE)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22N11%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.AutoFill%20Destination%3A%3DRange(%22N11%3AN%22%20%26amp%3B%20Range(%E2%80%9CM%E2%80%9D%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Range(Selection%2C%20Selection.End(xlDown)).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*My%20Problem%3A%3C%2FP%3E%3CP%3E1.AutoFill%20%3CSPAN%3EAlways%20stops%20at%2035%20rows.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2.It%20work%20only%20in%20Column%20L%20%5BList%20Column%3A%201%2C2%2C3.....%5D%20but%20doens't%20work%20in%20Columns%20like%20N%20%5BFormulated%3A%20VLOOKUP()%5D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3.%20Column%20J%20and%20M%20are%20the%20same%2C%20but%20M%20is%20in%20the%20table%20%5BM11%3AM%5D%20and%20J%20is%20in%20the%20DataBase%20%5BSame%20Sheet%5D.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E4.%20If%20I%20use%20%22J%22%26nbsp%3BIt%20always%20stops%205%20rows%20before%20the%20maximum%20%5BExm%3A%20If%20J%20has%2050%20Row%2C%20AutoFill%20Stop%20in%2045%5D.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDo%20you%20have%20any%20clue%20of%20this%20problem%3F%20(I%20can%20put%20All%20Code%20if%20Want%20or%20WorkBook%2C%20but%20It'll%20be%20more%20long).%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328144%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328144%22%20slang%3D%22en-US%22%3EI've%20done%20it!%3CBR%20%2F%3E%3CBR%20%2F%3E'Copy%20above%20formula%20to%20last%20row%20of%20Step%202%20sheet%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22A3%3AA%22%20%26amp%3B%20Worksheets(%22Step%202%22).Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3EDuan%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1329006%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1329006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629738%22%20target%3D%22_blank%22%3E%40Moses76%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20late%20reply!%3C%2FP%3E%3CP%3EIt%20works%20without%20error%20in%20my%20own%20workbook.%3C%2FP%3E%3CP%3ECould%20you%20provide%20a%20screenshot%20of%20the%20error%20and%20the%20worksheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1329046%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1329046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631249%22%20target%3D%22_blank%22%3E%40Duan_90%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20late%20reply!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EGlad%20to%20hear%20that%20you%20did%20that.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331185%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631377%22%20target%3D%22_blank%22%3E%40Mauriq25%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20there%20are%20some%20issues%20in%20your%20original%20workbook!%3C%2FP%3E%3CP%3EIf%20you%20can%20attach%20it%20or%20a%20sample%20of%20it%2C%20this%20will%20be%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332336%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20Photo%20of%20my%20Problem%3A%26nbsp%3B%26nbsp%3B%3CIMG%20src%3D%22https%3A%2F%2Fi.imgur.com%2FXVLPqXl.png%22%20border%3D%220%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20there%20would%20attach%20a%20DataExample%20(February)%20and%20the%20Code%20in%20other%20Sheet.%3C%2FP%3E%3CP%3EI've%20test%20February%20in%20the%20Original%20Excel%2C%20in%20other%20Excel%20and%20in%20the%20Test%20Excel%20and%20it%20don't%20work%20%3AS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20by%20your%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332446%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631377%22%20target%3D%22_blank%22%3E%40Mauriq25%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20inspecting%20the%20code%2C%20I%20found%20some%20lines%20that%20delete%20the%20last%20records!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Clear%20Contents%20Bug.png%22%20style%3D%22width%3A%20789px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186128i2DC942FECA7D0225%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Clear%20Contents%20Bug.png%22%20alt%3D%22Clear%20Contents%20Bug.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I've%20turned%20them%20to%20comments%20in%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332534%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332534%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20say%20that%20I%20love%20you%3F%3C%2FP%3E%3CP%3EThanks%20bro!!!%20I%20never%20find%20that%20there%20was%20some%20%22Deleting%20Codes%22%2C%20I've%20made%20it%20by%20macros%20and%20then%20Editing%20hehehe.%3C%2FP%3E%3CP%3EI'll%20still%20working%20in%20it%20and%20somes%20futures%20dudes%20I'll%20tell%20you%20%3B).%3C%2FP%3E%3CP%3EHave%20a%20nice%20day.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332594%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631377%22%20target%3D%22_blank%22%3E%40Mauriq25%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379614%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379614%22%20slang%3D%22en-US%22%3EHi%20%2C%3CBR%20%2F%3EI%20have%20a%20situation%20where%20I%20have%20to%20autofill%20towards%20the%20right%20of%20like%20consider%20a%20cell%20E4%20i%20have%20to%20autofill%20towards%20the%20right%20of%20it%20till%20it's%20below%20row%20has%20data.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1387225%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1387225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaytham%2C%20thank%20you%20for%20this%20code.%3C%2FP%3E%3CP%3EQuestion%20-%26nbsp%3BI'm%20in%20the%20process%20of%20writing%20a%20macro%20in%20VB%20and%20I%20need%20this%20code%20to%20copy%20a%20vlookup%20string%20value%20from%20cell%20AU2%20and%20keep%20pasting%20it%20in%20the%20cell%20directly%20below%20it%20until%20reaching%20a%20cell%20with%20data.%20I'd%20then%20like%20it%20to%20skip%20that%20non%20blank%20cell(s)%20and%20continue%20pasting%20into%20blank%20cells%20in%20this%20range%20until%20reaching%20the%20last%20row.%20When%20deciding%20to%20%22paste%22%20the%20macro%20should%20check%20Column%20A%20row%20by%20row%20and%20when%20there%20is%20no%20more%20data%20in%20Column%20A%20-%20the%20macro%20should%20end.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20express%20this%20logic%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1387579%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1387579%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663265%22%20target%3D%22_blank%22%3E%40Thomas2170%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20AutoFillToRight()%0A%20%20%20%20Dim%20lastCellFromRight%20As%20String%0A%20%20%20%20ActiveCell.Select%0A%0A%20%20%20%20lastCellFromRight%20%3D%20Selection.End(xlDown).End(xlToRight).Offset(-1%2C%200).Address%0A%20%20%20%20%0A%20%20%20%20If%20lastCellFromRight%20%3D%20%22%24XFD%241048575%22%20Or%20lastCellFromRight%20%3D%20%22%24IV%2465535%22%20Then%20Exit%20Sub%0A%20%20%20%20%0A%20%20%20%20Range(ActiveCell.Address%2C%20lastCellFromRight).FillRight%0A%20%20%20%20Range(lastCellFromRight).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20need%20to%20select%20the%20starting%20cell%20before%20you%20run%20it%20which%20in%20the%20example%20below%20cell%20(E3)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20854px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191453i8715AAF0489D17C3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388035%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666720%22%20target%3D%22_blank%22%3E%40spclfx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-basic%22%3E%3CCODE%3ESub%20FillOnlyBlankCellsWithFormula()%0A%20%20%20%20Range(%22AU2%3AAU10000%22).Select%0A%20%20%20%20Selection.SpecialCells(xlCellTypeBlanks).Select%0A%20%20%20%20Selection.Formula%20%3D%20%22%3DVLOOKUP(B1%2C%24D%249%3A%24E%2411%2C2%2C0)%22%0A%20%20%20%20Range(%22AU2%22).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20change%20the%20formula%20to%20match%20your%20existing%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1390466%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Haytham!%26nbsp%3BYour%20code%20worked%20but%20I%20mis-explained%20something.%20My%20first%20set%20of%20instructions%20pastes%20a%20vlookup%20string%20in%20all%20the%20cells%20in%20Range%20AU.%20When%20your%20code%20runs%2C%20it%20%22sees%22%20these%20values%20and%20considers%20the%20cell%20to%20be%20non%20blank%20so%20nothing%20happens.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20my%20problem%20-%20I%20need%20to%20check%20a%20column%20for%20a%20set%20of%20about%2010%20Group%20tags%20and%20then%20specify%20which%20owner%20should%20be%20assigned%20to%20each%20tag%20in%20column%20AU.%20When%20I%20built%20this%20macro%20below%2C%20I%20inputted%20one%20vlookup%20that%20accurately%20searches%20but%20it%20only%20gives%20me%20one%20owner%20and%20the%20same%20value%20is%20pasted%20down%20the%20whole%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20remaining%209%20tags%20and%20owners%2C%20I%20need%20to%20build%20logic%20to%20recurse%20through%20a%20set%20of%20tag%20names%20so%20that%20I%20can%20identify%26nbsp%3Bthe%20owner%20in%20column%20AU.%3C%2FP%3E%3CP%3EBelow%20is%20my%20code...%20what%20are%20your%20thoughts%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'***************************************************************************************************%3CBR%20%2F%3E'This%20section%20will%20lookup%20owner%20names%20against%20the%20tags%20specified%20in%20the%20column%20called%20%22OWNERS%22.%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Goto%20Reference%3A%3D%22R2C47%22%3CBR%20%2F%3ERange(%22AU1%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22LOOKUP%20OWNER%22%3CBR%20%2F%3ERange(%22AU2%22).Select%3CBR%20%2F%3EActiveWindow.SmallScroll%20Down%3A%3D-9%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%22%3DIF(ISNUMBER(SEARCH(%22%22GROUP%20A%22%22%2CRC%5B-1%5D))%2C%22%22JOHN%20DOE%22%22%2C%22%22%22%22)%22%3C%2FP%3E%3CP%3E'%20ActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%22%3DIF(ISNUMBER(SEARCH(%22%22GROUP%20B%22%22%2CRC%5B-1%5D))%2C%22%22LITTLE%20TIMMY%22%22%2C%22%22%22%22)%22%3CBR%20%2F%3E'***************************************************************************************************%3CBR%20%2F%3E'This%20code%20copies%20the%20above%20VLookup%20Statement%20down%20the%20entire%20Range%20until%20the%20last%20row%20containing%20data.%20This%20is%20using%20column%20AT%20as%20the%20indicator%20for%20more%20or%20end%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelection.AutoFill%20Destination%3A%3DRange(%22AU2%3AAU%22%20%26amp%3B%20Range(%22AT%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3E'***************************************************************************************************%3C%2FP%3E%3CP%3E'%20This%20section%20will%20only%20copy%20code%20into%20blank%20cells%20in%20range%20AU%3CBR%20%2F%3E'This%20failed%20because%20the%20cells%20in%20range%20AU%20have%20the%20vlookup%20string%20from%20the%20previous%20section.%3CBR%20%2F%3E%3CBR%20%2F%3E'FillOnlyBlankCellsWithFormula()%3CBR%20%2F%3ERange(%22AU2%3AAU1000%22).Select%3CBR%20%2F%3ESelection.SpecialCells(xlCellTypeBlanks).Select%3CBR%20%2F%3ESelection.Formula%20%3D%20%22%3DIF(ISNUMBER(SEARCH(%22%22GROUP%20B%22%22%2CRC%5B-1%5D))%2C%22%22LITTLE%20TIMMY%22%22%2C%22%22%22%22)%22%3CBR%20%2F%3ERange(%22AU2%22).Select%3CBR%20%2F%3E'***************************************************************************************************%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20list%20of%20tag%20values%20I%20am%20searching%20against.%20Each%20group%20has%20an%20owner%20name%20that%20I%20am%20manually%20inputting%20into%20the%20vlookup%20statement%20above.%3C%2FP%3E%3CP%3ECOLUMN%20%22AT%22%20CONTAINS%20THESE%20GROUP%20NAMES%3C%2FP%3E%3CP%3EGROUP%20A%3CBR%20%2F%3EGROUP%20B%3CBR%20%2F%3EGROUP%20C%3CBR%20%2F%3EGROUP%20D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1398206%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1398206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666720%22%20target%3D%22_blank%22%3E%40spclfx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20attach%20the%20worksheet%20you%20working%20on%20or%20a%20sample%20of%20it%3F%3C%2FP%3E%3CP%3ESo%20I%20can%20reproduce%20the%20issue%20and%20figure%20it%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399496%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399496%22%20slang%3D%22en-US%22%3EThanks%40Haytham%2C%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20i%20m%20facing%20one%20issue%20that%20it%20is%20autofilling%20as%201%20I%20want%20it%20be%20filled%20in%20series.%20Like%201%2C2%2C3%20and%20so%20on%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1401946%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1401946%22%20slang%3D%22en-US%22%3EPlease%20also%20could%20you%20help%20me%20with%20the%20same%20for%20autofill%20ing%20down%20in%20series%20depending%20on%20data%20in%20the%20coloumn%20beside%20it%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413532%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20VBA%20to%20Autofill%20a%20Row%20until%20the%20end%20of%20the%20number%20of%20data%20in%20another%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663265%22%20target%3D%22_blank%22%3E%40Thomas2170%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20this%20below%20instead%2C%20and%20tell%20me%20what%20you%20think.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20AutoFillToRight()%0A%20%20%20%20Dim%20lastCellFromRight%20As%20String%0A%20%20%20%20ActiveCell.Select%0A%0A%20%20%20%20lastCellFromRight%20%3D%20Selection.End(xlDown).End(xlToRight).Offset(-1%2C%200).Address%0A%20%20%20%20%0A%20%20%20%20If%20lastCellFromRight%20%3D%20%22%24XFD%241048575%22%20Or%20lastCellFromRight%20%3D%20%22%24IV%2465535%22%20Then%20Exit%20Sub%0A%20%20%20%20%0A%20%20%20%20Range(ActiveCell.Address%2C%20lastCellFromRight).FillUp%0A%20%20%20%20Range(lastCellFromRight).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello,

 

I need some help with the following problem:

 

The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to.

 

The situation looks like this:

 

2017-12-14_09h25_21.png

 The code that I have so far looks like this:


    Sheets(3).Select
    Range("AY2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
    Selection.AutoFill Destination:=Range("AY2:AY1662")
    Range("AY2:AY1662").Select

 

The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662.

 

I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E.

 

Thank you very much in advance,

Kai
   

 

 

62 Replies
Highlighted

Replace these lines of code:

Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select

With this:

Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Give it a try!

And provide me with any feedback!

Highlighted

@Haytham Amairah 

 

Hi Haytham Amairah,

 

I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from: 

 

Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Select

 

to:

Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

 

Highlighted
Thanks Haytham,
This worked like a magic for me in no time.
Appreciate your knowledge and interest to share it.
Highlighted
Highlighted

@Haytham Amairah Dear Haytham, would you be able to amend my code so that it works the same way as OP's please?

 

I have to amend the ActiveCell.End(xlDown) part so that the formula (in column N) stops at the last row that contains data in column M. Thank you in advance.

 

ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
ActiveCell.Select
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown))
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(ActiveCell).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

Regards,

Mike

Highlighted

Hi Mike,

 

Please try this one:

Sub test()

ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
Selection.AutoFill Destination:=Range("N1:N" & Range("M" & Rows.Count).End(xlUp).Row)
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

 

Hope that helps

 

@W4rcloud 

Highlighted

Hi

I am trying to build a macro to unprotect sheet, autofill columns from above and protect sheet again. I am using below formula however it is giving error. Can anyone help please?

Sub sbUnProtectSheet()

ActiveSheet.Unprotect "9999"

Set SourceRange = ActiveSheet.Range("B10:H10")
Set fillRange = ActiveSheet.Range("B11:H39")
SourceRange.AutoFill Destination:=fillRange
ActiveSheet.Protect "9999", True, True

End Sub

Thanks

Anil

Highlighted

@AnilChhabra

 

Hi,

 

It's better to post your question as a new conversation in the community.

If you can, provide us with a sample of the worksheet you're working on!

 

Regards

Highlighted

@Haytham Amairah 

 

Hey Haytham,

 

I'm really hoping you can point me in the right direction as well. 

 

I'm trying to do the same thing as OP, however I have blank rows above and to the left of my data and not sure if that is throwing it off. Below is a screenshot (had to switch all values to xxxx for privacy purposes), I want my Macro to create a new Column to the left of Column J in the screenshot, where a new Column J will be created that will house my formula beginning in the new Cell J6. I then want the Macro to extend this formula down the last row in any of the columns to the left of Column J, as they will all always be populated, however I have been tying everything to Column I in my troubleshooting.

 

Macro Help.PNG

 

Here is the snip of code that I know is causing the issue, with the problem rows likely being the three rows below my IF formula.

 

Sheets("Raw_Data").Select
ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "MEASURE OUTCOME"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A366")
ActiveCell.Range("A1:A366").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Met", _

 

Are you able to help me re-write this code to have the Macro fill the IF formula down to the last row of data rather than just the last cell in my current selection? The number of rows will change on a daily basis.

 

Thank you!

Highlighted

@Kai El Harrar 

Hi

If you wee to create a Table instead, Insert > Table, then your formula would automatically be inserted as you add rows to the Table, without the need for any VBA code.

Highlighted

@Steven1835

 

Hi,

 

Sorry about the late reply!

I think you need this one:

Sub FillFormulaDown()
'To check if the column is already exist
If Application.WorksheetFunction.IsFormula(Range("J5").Offset(1, 0)) Then
'skip
Else
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J5").Value = "MEASURE OUTCOME"
End If

Range("J5").Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
Selection.AutoFill Destination:=Range("J6:J" & Range("I" & Rows.Count).End(xlUp).Row)
ActiveCell.EntireColumn.AutoFit
End Sub

 

You will find it in the attached workbook.

Please test it and tell me what you thought.

 

Hope that helps

Highlighted

@Haytham Amairah 

 

Hi Haytham,

 

I have used your code in my macro when it imports from a text file and reorganizes the records. As part of it, it is supposed to copy down rows of a table as below:

 

ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

 

This works nicely but if there is a single line of record in the file, it copies the row but then it adds a second row, copies down the first row in column A and B then returns a VB Error as shown in the attached screenshot.

 

Any ideas how to deal with it?

 

Thanks

 

Nick

Highlighted

@NickNick33 

 

 Hi,

 

To figure out the problem, please provide a sample of the data you work on.

 

Regards

Highlighted

@Haytham Amairah 

 

Sure, it is attached. Th eoriginal file is a tsv file but the upload tool would not accept tsv so I had to change it to csv. You will need to change it to tsv for the Excel file to read it. 

 

Thank you 

Highlighted

@NickNick33

 

Hi,

 

This is what I got after opening your file:

Screenshot_1.png

 

Sorry, but the data isn't clear and I'm not sure what you're trying to do!

Based on your macro, you want to fill down Column A & B based on the number of rows in column E ??

Highlighted

@Haytham Amairah 

 

Here are the macro steps:

  • Imports the source file I provided,
  • adds 2 columns (Shipmentid and Name) in addition to the columns in the source file.
  • It takes what is in B1 in the source as Shipmentid and copies down the new column A
  • It takes what is in B2, copies down the new column B
  • Deletes the top rows up to the headings so that the whole file becomes one uniform table. 

This works fine except when there is a single in the source. I have attached both the Excel file and the different files that are used as the source. 

 

The macro is created to import the file called current.tsv. We just swap the files we want to import by renaming them "current.tsv". So I provided one sith single line and another with multiple. The one with multiple works fine but single one returns error as the VBA seems to have a problem with it. 

 

Curious situation. I hope this helps. 

 

Highlighted

@NickNick33

 

Hi,

 

After I reproduced this issue, I noticed that the error also occurs even if you have two records in the imported file.

 

If you have one or two rows in the imported file, there is no need to use the AutoFill method in the macro because all rows in the table are already filled.

If you use it in this case, there will be an error!

 

The solution is to make the code smart somehow to see if the imported file has less than three rows and if so, skip the autofill process to prevent the error.

 

This is what I suggest to overcome this issue:

Sub PrepFile()
'
' PrepFile Macro
'

'
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=current;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [current]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "current"
.Refresh BackgroundQuery:=False
End With
Range("K10").Select
ActiveCell.FormulaR1C1 = "Shipment ID"
Range("L10").Select
ActiveCell.FormulaR1C1 = "Shipment Name"
Range("B2").Select
Selection.Copy
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Copy
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:10").Select
Range("A10").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("K:L").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

If Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 1 Then
Range("A1").Select
Exit Sub
ElseIf Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 2 Then
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Exit Sub
End If

Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
End Sub

 

Please replace your old macro with it.

 

Hope that helps

Highlighted

@Haytham Amairah 

What do I have to add if the source isn't in the same worksheet?

 

Thanks

@To31416

 

What's the source?
Please explain the problem well
!