Count number of rows in a range using Excel and VBA

Count number of rows in a range. This tutorial shows how to count the total number of rows from a selected range through the use of an Excel formula or VBA. Skiptocontent Countnumberofrowsinarange ThistutorialshowshowtocountthetotalnumberofrowsfromaselectedrangethroughtheuseofanExcelformulaorVBA EXCELVBAEXPLANATION Example:Countnumberofrowsinarange METHOD1.Countnumberofrowsinarange EXCEL EditFormula =ROWS(E5:E15) ThisformulausestheExcelROWSfunctiontoreturnthenumberofrowsintheselectedrange.Inthisexamplewehaveselectedrange(E5:E15)insidetheROWSfunction,whichreturnsatotalof11rows. METHOD1.CountnumberofrowsinarangeusingVBA VBA EditVBACode SubCount_number_of_rows_in_range() 'declareavariable DimwsAsWorksheet Setws=Worksheets("Analysis") 'countthetotalnumberofrowsinaspecificrange ws.Range("B5")=ws.Range("E5:E15").Rows.Count EndSub ADJUSTABLEPARAMETERS OutputRange:Selecttheoutputrangebychangingthecellreference("B5")intheVBAcode. Range:Selecttherangefromwhichyouwanttocountthenumberofrowsbychangingtherangereference("E5:E15")intheVBAcode. WorksheetSelection:SelecttheworksheetinwhichyouwanttocountthenumberofrowsfromaselectedrangebychangingtheAnalysisworksheetnameintheVBAcode.Youcanalsochangethenameofthisobjectvariable,bychangingthename'ws'intheVBAcode. Explanationabouttheformulausedtocountnumberofrowsinarange EXPLANATION EXPLANATION ThistutorialshowshowtocountthetotalnumberofrowsfromaselectedrangethroughtheuseofanExcelformulaorVBA. BoththeExcelformulaandVBAapproachmakeuseoftheROWSfunctiontocountthenumberofrowsinaselectedrange. UsingtheVBAmethodyouwillalsoneedtocombinetheRowsfunctionwiththeCountfunctiontoreturnthetotalnumberofrowsinaselectedrange. FORMULA =ROWS(array) ARGUMENTS array:Anarrayorreferencetoarangeofcells. RELATEDTOPICS RelatedTopic Description RelatedTopicandDescription Countnumberofcolumnsinarange HowtocountthetotalnumberofcolumnsinarangeusingExcelandVBAmethods Countnumberofcellsinarange HowtocountthetotalnumberofcellsinarangeusingExcelandVBAmethods Countnumberofcharactersinacell Howtocountthetotalnumberofcharacters,includingspaces,inacellusingExcelandVBAmethods Countnumberofcharactersinacellexcludingspaces Howtocountthetotalnumberofcharactersinacell,excludingspaces,usingExcelandVBAmethods Countnumberofcharactersinarange Howtoccountthetotalnumberofcharactersinarange,includingspaces,usingExcelandVBAmethods RELATEDFUNCTIONS RelatedFunctions Description RelatedFunctionsandDescription ROWSFunction TheExcelROWSfunctionreturnsthenumberofrowsinaspecifiedarray