Excel VBA: Dynamic Range Based on Cell Value (3 Methods)

This article illustrates how to define a dynamic range in Excel based on a cell value using VBA code with 3 different examples. Home»Macros&ExcelVBA»ExcelVBA:DynamicRangeBasedonCellValue(3Methods) Macros&ExcelVBA ExcelVBA:DynamicRangeBasedonCellValue(3Methods) AlArafatSiddique Jun2,2022 0 ThisarticleillustrateshowtodefineadynamicrangeinExcelbasedonacellvalueusingVBAcode.We’regoingtoexplain3differentexamplestodefineandselectadynamicrangewiththenecessaryexplanation. TableofContents hide DownloadPracticeWorkbook 3ExamplesofDefiningDynamicRangeBasedonCellValue 1.SelectDynamicRangeBasedontheValueofAnotherCellUsingVBAinExcel 2.DefineDynamicRangeBasedonCellValuesUsingVBAinExcel 3.CopyandPasteDynamicRangeBasedonCellValuewithExcelVBA ThingstoRemember Conclusion RelatedArticles DownloadPracticeWorkbook Downloadthispracticeworkbooktoexercisewhileyouarereadingthisarticle. DynamicRangeBasedonCellValue.xlsm 3ExamplesofDefiningDynamicRangeBasedonCellValue Thefollowingsectiondescribeshowtoopenandwritecodeinthevisualbasiceditor. WriteCodeinVisualBasicEditor Followthestepstoopenthevisualbasiceditorandwritesomecodethere. GototheDevelopertabfromtheExcelRibbon. ClicktheVisualBasicoption. IntheVisualBasicForApplicationswindow,clicktheInsertdropdowntoselecttheNewModuleoption. Nowthatanewmoduleisopened,writesomecodethereandpressF5torun. 1.SelectDynamicRangeBasedontheValueofAnotherCellUsingVBAinExcel Let’ssaywehavealistthatcontainsabunchofcitynamesincellsA1:A7withtheircountrynamesinthenextcolumn(cellsB1:B7).Wewanttoconfigureacodethatwillselectmultiplerowsfromthesetwocolumns.Butthenumberofrowstobeselectedshouldnotbehardcoded,rather,wewanttomakeitdynamicbasedonacellvalue. HereincellE3,we’regoingtoputthenumberofrows(inthisexample,3)toselect.Todothis,copyandpastethefollowingcodeintothevisualcodeeditor. SubDynamicRangeBasedOnCellValue() DimDValueAsVariant DimDRangeAsRange DValue=ActiveSheet.Range("E3").Value SetDRange=ActiveSheet.Range("A1:B"&DValue) DRange.Select EndSub NowpressF5torunthecode. Asanoutput,we’vesuccessfullyselectedthefirst3rowsofthedataset.Ifweput5incellE3andrunthecodeagain. That’showwecanchangethedynamicrangetobeselectedbyrunningtheVBAcode. ReadMore:ExcelDynamicRangeBasedonCellValue 2.DefineDynamicRangeBasedonCellValuesUsingVBAinExcel Inthisexample,we’llshowhowtodefineandthenselectadynamicrangebasedontwocellvaluesi.e.,onecellvaluetodefinethestartingandanothertoendingofthedynamicrange.Toillustratethis,let’scountinthefollowingdatasetrangingfromA1:H12.Inyourcase,itcanbemultipletimeslargerthanthisone.We’vespecifiedtwocellsintheworksheet-B15andC15,fromwherewe’regoingtotakethestartingandendingrangedataofourdynamicrangeinourVBAcode. Tosolvetheaboveissue- Weneedtodefinetwocellsthatholdthestartingandendofthedynamicrange.HerewedefinedcellsB15andC15toholdthestartingandendoftherangei.e.,A1toH5inthisexample. Thenweneedtocopyandpastethefollowingcodeintothevisualcodeeditor. SubSelectDynamicRange()  DimsRangeAsString,eRangeAsString  sRange=ActiveSheet.Range("B15"):  eRange=ActiveSheet.Range("C15")  Range(sRange&":"&eRange).Select EndSub Inthiscode,wedeclaredtwodifferentvariablesnamedsRangeandeRangetoholdthestartingandendcellreferenceofthedynamicrangeincellsB15andC15respectively. PressF5torunthecode.We’vesuccessfullyselectedtherangeofRangelA1:H5. IfwechangethevaluesofcellsB15andC15tosomeothervalue,theselectedrangewillchangeaccordingly. ReadMore:CreateDynamicSumRangeBasedonCellValueinExcel(4Ways) SimilarReadings ExcelDynamicNamedRange[4Ways] CreateDynamicNamedRangewithVBAinExcel(Step-by-StepGuideline) DataValidationDropDownListwithExcelTableDynamicRange CreateaDynamicChartRangeinExcel(2Methods) 3.CopyandPasteDynamicRangeBasedonCellValuewithExcelVBA Inthisillustration,wehaveademosalesinfotemplatethatoneshopmanagerhastocreateeverydaytostoresalesinformation.UsingVBAcode,we’regoingtocopyandpastethetemplatetoanewworksheetbasedonacellvaluethatholdstheworksheetname.Inaddition,we’llbeabletodecidewhatportionofthetemplateshouldbecopiedandthenpastedtoourdesiredworksheet.Hereisthetemplateinthebelowscreenshotwhichisinthe“Source”worksheet. Tofacilitatetheusertochoosewhatcolumnstocopyinthenewworksheet,weinsertedthecolumnnamesincellsG2:G6.AndincellH2,weputthesourceworksheetname,formwherewe’regoingtocopythetemplatetoanewworksheet.WeneedtoputthenewworksheetnameincellI2. Let’ssaywe’vecreatedanewworksheetforDay1ofamonthnamed“Day1”.IncellI2,weputthenewworksheetname.Inaddition,incellsG2:G6weputthecolumnnamestocopyintheworksheetnamedDay1. Nowcopyandpastethefollowingcodeinthevisualcodeeditor. SubCopyPasteDynamicRange()    DimCSheetAsWorksheet,PSheetAsWorksheet    DimCRangeAsRange,PRangeAsRange    DimCLastRowAsLong,CLastColAsLong,PLastCol    DimColArrayAsVariant    DimColNameAsVariant    DimColNoAsLong    Dima,bAsString    a=ActiveSheet.Range("H2").Value    b=ActiveSheet.Range("I2").Value    SetCSheet=Sheets(a)    SetPSheet=Sheets(b)    CLastRow=CSheet.Range("A"&Rows.Count).End(xlUp).Row         'lastrow    CLastCol=CSheet.Cells(1,Columns.Count).End(xlToLeft).Column  'lastcolumn    WithCSheet        SetCRange=.Range(.Cells(1,"A"),.Cells(CLastRow,CLastCol))    EndWith    PLastCol=PSheet.Cells(1,Columns.Count).End(xlToLeft).Column  'lastcolumn    ColArray=Range("G2:G6").Value'columnheaders    ForEachColNameInColArray        ColNo=Application.Match(ColName,CRange.Rows(1),0)        PLastCol=PLastCol+1        CRange.Columns(ColNo).CopyDestination:=PSheet.Cells(1,PLastCol)    NextColName EndSub PressF5torunthecode.Nownavigatetotheworksheetnamed“Day1”.Theoutputisherebelow. Similarly,wecanmakeanotherworksheetforDay2andrunthecodeagainafterchangingtheI2cellvaluetoDay2. Theoutputis- Nowlet’ssayweneedtocopythetemplatewithouttheTax(%)columnintheDay3worksheet.Forthis,weneedtosetupthevalueslikethis. Intheoutput,intheDay3worksheet,wehavethetemplatebutwithouttheTaxcolumn. ReadMore:HowtoUseDynamicRangeforLastRowwithVBAinExcel(3Methods) ThingstoRemember IntheVBAcode,weusedtheRange.Selectmethodtoselectthedynamicrange. Conclusion Now,weknowhowtodefineadynamicrangeinExcelbasedonacellvalueusingVBAcode.Hopefully,itwouldhelpyoutousethesemethodsmoreconfidently.Anyquestionsorsuggestionsdon’tforgettoputtheminthecommentboxbelow. RelatedArticles HowtoCreateaRangeofNumbersinExcel(3EasyMethods) ExcelOFFSETDynamicRangeMultipleColumnsinEffectiveWay ExcelDynamicNamedRangeBasedonCellValue(5EasyWays) OFFSETFunctiontoCreate&UseDynamicRangeinExcel SaveSavedRemoved0 Tags:ExcelDynamicRange AlArafatSiddique Hello!ThisisArafat.HereI'mresearchingMicrosoftExcel.IdidmygraduationfromBangladeshUniversityofEngineeringandTechnology(BUET).Myinterestindatascienceandmachinelearningalluredmetoplaywithdataandfindsolutionstoreal-lifeproblems. Iwanttoexplorethisdata-drivenworldandmakeinnovativesolutions. RelatedArticles AddedtowishlistRemovedfromwishlist0 HowtoCreateaProgressBarWhileMacroIsRunninginExcelVBA AddedtowishlistRemovedfromwishlist0 [Solved]:UserDefinedTypeNotDefinedinExcelVBA(2QuickSolutions) AddedtowishlistRemovedfromwishlist0 HowtoUseMacrotoCleanUpDatainExcel(4EasyMethods) AddedtowishlistRemovedfromwishlist0 ExcelVBA:CopyCellValueandPastetoAnotherCell Wewillbehappytohearyourthoughts LeaveareplyCancelreply Δ AffiliateDisclosure Thispostmaycontainaffiliatelinks,meaningwhenyouclickthelinksandmakeapurchase,wemayearnanaffiliatecommission,butthisneverinfluencesouropinion. 100+Pre-BuiltExcelTemplates AboutExcelDemy.com ExcelDemyisaplacewhereyoucanlearnExcel,DataAnalysis,andotherOfficerelatedprograms.Weprovidetips,howtoguideandalsoprovideExcelsolutionstoyourbusinessproblems. ExcelDemy.comisaparticipantintheAmazonServicesLLCAssociatesProgram,anaffiliateadvertisingprogram.IearnasmallcommissionifyoubuyanyproductsusingmyaffiliatelinkstoAmazon. Contact|PrivacyPolicy CategoriesCategories SelectCategory AdvancedExcel DataAnalysiswithExcel ExcelBasics ExcelBooks ExcelCharts ExcelforFinance ExcelFormulas ExcelFunctions ExcelPivotTable ExcelPowerQuery ExcelSolver ExcelTemplates ExcelTraining&Courses Macros&ExcelVBA Software Uncategorized SearchinExcelDemy.com