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

@Haytham Amairah

 

Thanks for your quick reply!

 

I have an Excel file in which I import various reports and in this file I have a sheet, where I use those data for my formulas.

The various reports I import have the same length but differ in length every day.

 

In my main sheet where I use the imported data the first row always stays the same with all kind of formulas. However, I want my Macro to autofill the other rows until there is no more data in one of the imported reports.

 

Right now I have to set the autofill range manually. So if the report is too long, it's either cut or if it's too short I get errors.

 

Hope that was understandable.

 

Kind regards

Highlighted

@To31416

 

It seems that this situation is different!

 

But I think you need to somehow get the total number of rows in the imported reports and then use that number in the macro.

 

If you can attach your file or a sample of it, this is would be helpful!

Highlighted

@Haytham Amairah 

 

Thank you for helping me out with this.

 

I removed all data and a settings sheet but the important data is still there.

The macro-button is in the sheet "Output" Cell "F1"

 

The length should be the same as I got days in the reports. The totals column excluded.

Highlighted

@To31416

 

Hi,

 

I've updated the code behind the Load button to fill down the rows based on the number of rows in (yield) sheet.

 

This is the code:

Sub FillRowsDown()
    'Get the last row in sheet (yield) and save it in the lastRow variable
    Dim lastRow As Long
    lastRow = Sheets("yield").Range("A" & Rows.Count).End(xlUp).Row
   
    'Subtract the first 6 rows from the lastRow
    lastRow = lastRow - 6
    
    'Subtract the Total row (if exists)
    If LCase(Sheets("yield").Range("A" & Rows.Count).End(xlUp).Value) = "total" Then
        lastRow = lastRow - 1
    End If
    
    With Sheets("output")
     .Range("A4:U4").AutoFill Destination:=Range("A4:U" & lastRow + 3)
     .Range("D4").Select
    End With
End Sub

 

Hope that helps

Highlighted

@Haytham Amairah 

thank you so much!

 

you really were of great help

Highlighted

@Haytham Amairah 

 

Hi Haytham,

 

If I use to populate Row H to length of Row E

 

 

Sub Test()

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

 

 

 

What would I need to add/change to get content from single Cell A2 and fill down from next empty in H

 

(the content of A2  changes and would like to see all changes updated in H rather than just overwriting) 

 

Highlighted

@aimeelou

 

Hi,

 

If I understand you well, nothing has to change in the code!

You need to use the below formula in cell H2 to link this cell to cell A2.

=A2

 

After then, run the code so that you fill this formula down until the last row in column E and make the column H and A linked together.

 

Regards

Highlighted

@Haytham Amairah 

 

Thanks for your help Haytham,

unfortunately I am probably doing something wrong as when I link the Cells then run macro rather than

filling down with the text that is in A2 - cells in H fill down with a zero

Highlighted

@aimeelou

 

Could you attach a sample of the data you working on?

Highlighted

@Haytham Amairah 

 

Thank you so much for helping

Highlighted

@aimeelou

 

If you need to fill the column H with the content of only cell A2 so that each cell in column H will be filled with the same text in cell A2.

Then you just need to lock the formula in cell H2 as follows:

=$A$2

 

Highlighted

@Haytham Amairah 

 

Hi Amairah,

 

I have a question somewhat similar to the post above.

Currently, I want to create a column with a function and want to autofill the column with the function until the end of the row

However, I wish to put in the number to rows so that it works on any worksheet (maybe with different number of rows)

 

Screenshot below is the formula I want to put into the column and autofill until the end of the row for any worksheet.

 

clipboard_image_1.png

Also, this is what I have so far 

 

Sub MacroCode_Column fill

 

Dim myFirstColumn As Integer
Dim myLastColumn As Integer
Dim myFirstFieldRow As Integer
Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer


myFirstFieldRow = 5
myFirstDataColumn = Cells(5, Columns.Count).End(xlToLeft).Column + 1
myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

 

'Multiple Occurred

ActiveSheet.Cells(myFirstFieldRow, myFirstDataColumn + 6).Select
ActiveCell.Formula = "MCO Incurred"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"
Selection.AutoFill Destination:=Range(Cells(myFirstFieldRow + 1, myFirstDataColumn + 6), Cells(myLastDataRow, myFirstDataColumn + 6))

 

Thank you in advance!!

 

Highlighted

@D_Jang3884

 

Hi,

 

Based on what you want to fill the formula down?

You see that the solutions here in this conversation focus on filling the rows in a column based on the number of rows of another column.

Highlighted

@Haytham Amairah 

 

Hi Amairah,

 

Sorry if I asked a non-related question to the post but regarding to your question - basically I want to update the number of rows to the formula below using the following variables (which I created to set first row to be 5 and the last row to be determined based on data I'm using)

 

Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer

 

myFirstFieldRow = 5

myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row 

 

and the formula I want to update is below

 

ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"

 

Currently, the rows in the formula is in numbers but I wish to replace it using dimensions.

 

I would greatly appreciate it if you could help or direct me to where I can ask.

 

Thanks!!

Highlighted

@D_Jang3884

 

Hi,

 

I suggest you post this question in a separate post by starting a new conversation in here.

If you didn't find the answer, you may ask this community instead under [vba] tag.

 

Regards

Highlighted
Hi @Haytham, I'm trying to do the same as above to autofill multiple columns to varying rows and I have tried a couple of different things I've seen above, but I fear I don't have the excel coding knowledge to know what I'm doing wrong. the following is my macro and I'd like the "E145040" to be variable. I also dont know if I need to change the "$A$915118", "$C$915118", "$E$915118", "$G$915118" and "$AC$145040" as well. Thank you so much in advance! Sub Macro5() ' ' Macro5 Macro ' ' Range("Y1").Select ActiveCell.FormulaR1C1 = "COMP" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-21]=""Closed"",1,IF(RC[-21]=""Cancelled"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.Select ActiveCell.FormulaR1C1 = "PROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-22]=""Closed"",RC[-22]=""Verified"",RC[-22]=""Ready_To_Verify"",RC[-22]=""Cancelled""),1,IF(OR(RC[-22]=""Assigned"",RC[-22]=""Reassigned"",RC[-22]=""Pending""),IF(COUNTIF(RC[-11]:RC[-8],""Yes/Oui"")>0,1,0),0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "PC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-23]=""Reassigned"",RC[-23]=""Assigned"",RC[-23]=""Pending""),IF(COUNTIF(RC[-12]:RC[-9],""Yes/Oui"")>0,1,0),0)" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "VER" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-24]=""Verified"",1,IF(RC[-24]=""Ready_To_Verify"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "UNPROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-25]=""Closed"",RC[-25]=""Verified"",RC[-25]=""Ready_To_Verify"",RC[-25]=""Cancelled""),0,IF(OR(RC[-25]=""Assigned"",RC[-25]=""Pending""),IF(COUNTIF(RC[-14]:RC[-11],""Yes/Oui"")>0,0,1),1))" ActiveCell.Offset(0, -4).Range("A1:E1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:E145040") ActiveCell.Range("A1:E145040").Select Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=25, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("Z2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=26, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("C1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=27, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("E1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=28, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("G1").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False ActiveSheet.Range("$A$1:$A$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("C:C").Select ActiveSheet.Range("$C$1:$C$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("E:E").Select ActiveSheet.Range("$E$1:$E$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("G:G").Select ActiveSheet.Range("$G$1:$G$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=9, Criteria1:= _ "PA Exceptions – Exception PA" Cells.Select Range("J1").Activate Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet3!R1C1:R981068C29", Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _ :=xlPivotTableVersion15 Sheets("Sheet4").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable3").PivotFields("User ID") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("PROC"), "Sum of PROC", xlSum ActiveWindow.SmallScroll Down:=-84 Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets("Sheet1").Select Sheets("Sheet1").Name = "1.0 PA RAW" Sheets("Sheet4").Select Sheets("Sheet4").Name = "2.1 production" Sheets("Sheet3").Select Sheets("Sheet3").Name = "2.0 PA exceptions" Sheets("Sheet2").Select Sheets("Sheet2").Name = "1.1 Daily update" End Sub
Highlighted

@Haytham Amairah 

I tried your suggestion and it works fine, but not for cases where there is only 1 row of data or no data (only heading). I have a heading in row 1, formula in row 2 (cell E2) which I'm trying to copy to the end of the table using this code:

 

Selection.AutoFill Destination:=Range("E2:E" & Range("C" & Rows.Count).End(xlUp).Row)

 

Many thanks in advance for your advice.

Sarka

Highlighted

@jahoda

 

Hi,

 

Sorry for the late reply!

Please attach a sample of the data you're working to fit the code on it.

 

Regards

Hi Haitham

I am new to VBA and I am trying to amend the ActiveCell.End(xlDown) part so that the formula (in column A) stops at the last row that contains data in column A. and again starts to autofill from this column to the next contains data and goes on,

 

and thank you in advance.

 this is a screenshot:

clipboard_image_0.png

 

Sub autofill()

Dim Wad As String
Wad = Cells(Rows.Count, "A").End(xlUp).Row
Range("A30").Select
Selection.autofill Destination:=Range("A30:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select

Selection.autofill Destination:=Range("wad:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select
End Sub

 

Waddah

@Haytham Amairah 

Highlighted

@walshamasi

 

Hi Waddah,

 

Sorry for the late reply, but I need some clarifications as column A seems to have no formulas in it!

If you can attach the worksheet here, that would be better.

 

Regards