VBA Select, Row, Column | SuperExcelVBA

VBA Row and Column ... Row and Column are properties that are often used to obtain the numerical address of the first row or first column of a selection or a ... FIRSTSTEPS VBAExcel'sVBA VBAConfiguring VBAVBEEditor VBATheMacroRecorder VBAFirstMacro VBAComments VBAButtonswithMacro FUNDAMENTALS VBAVariables VBAOperators VBAIfThen...Else VBASelectCase VBAFor...Next VBADoLoop VBASubAndFunction VBAStrings VBAEvents VBAArray VBAForEach RANGE VBARange VBANamedRange VBASelect,Row,Column VBALastrow VBAResize VBAExcelFormulas USERFORM VBAUserForm VBAUserFormLayout VBAUserFormEvent VBAWorksheetInterface DEEPENING VBAErrorHandling VBALibraries VBATextFiles VBAPivotTable VBAEmail VBAScheduledExec. TUTORIAL EXERCISES ABOUTUS Language English Português Search SearchinSuperExcelVBA Chooseyourlanguage VBASelect,Row,Column VBASelectVBASelectCurrentRegionVBAActiveCellVBASelectionSelectionFillDownVBAEntireRowandEntireColumnVBARowsandColumnsVBARowandColumn VBASelect Itisverycommontofindthe.Selectmethodsinsavedmacrorecordercode,nexttoaRangeobject. .SelectisusedtoselectoneormoreelementsofExcel(ascanbedonebyusingthemouse)allowingfurthermanipulationofthem. Selectingcellswiththemouse: SelectingcellswithVBA: 'Range([cell1],[cell2]) Range(Cells(1,1),Cells(9,5)).Select Range("A1","E9").Select Range("A1:E9").Select Eachoftheabovelinesselecttherangefrom"A1"to"E9". VBASelectCurrentRegion Ifaregionispopulatedbydatawithnoemptycells,anoptionforanautomaticselectionistheCurrentRegionpropertyalongsidethe.Selectmethod. CurrentRegion.Selectwillselect,startingfromaRange,alltheareapopulatedwithdata. Range("A1").CurrentRegion.Select Makesuretherearenogapsbetweenvalues,asCurrentRegionwillmaptheregionthroughadjoiningcells(horizontal,verticalanddiagonal). Range("A1").CurrentRegion.Select Withalltheadjacentdata Notalladjacentdata "C4"isnotselectedbecauseitisnotimmediatelyadjacenttoanyfilledcells. VBAActiveCell TheActiveCellpropertybringsuptheactivecelloftheworksheet. Inthecaseofaselection,itistheonlycellthatstayswhite. Aworksheethasonlyoneactivecell. Range("B2:C4").Select ActiveCell.Value="Active" UsuallytheActiveCellpropertyisassignedtothefirstcell(topleft)ofaRange,althoughitcanbedifferentwhentheselectionismademanuallybytheuser(withoutmacros). TheAtiveCellpropertycanbeusedwithothercommands,suchasResize. VBASelection Afterselectingthedesiredcells,wecanuseSelectiontorefertoitandthusmakechanges: Range("A1:D7").Select Selection=7 Selectionalsoacceptsmethodsandproperties(whichvaryaccordingtowhatwasselected). Selection.ClearContents'Deletesonlythecontentsoftheselection Selection.Interior.Color=RGB(255,255,0)'Addsbackgroundcolortotheselection Asinthiscaseacellrangehasbeenselected,theSelectionwillbehavesimilarlytoaRange.Therefore,Selectionshouldalsoacceptthe.Interior.Colorproperty. RGB(RedGreenBlue)isacolorsystemusedinanumberofapplicationsandlanguages.Theinputvaluesforeachcolor,intheexamplecase,rangesfrom0to255. SelectionFillDown Ifthereisaneedtoreplicateaformulatoanentireselection,youcanusethe.FillDownmethod Selection.FillDown BeforetheFillDown AftertheFillDown .FillDownisamethodapplicabletoRange.SincetheSelectionwasdoneinarangeofcells(equivalenttoaRange),themethodwillbeaccepted. .FillDownreplicatestheRange/Selectionformulaofthefirstline,regardlessofwhichActiveCellisselected. .FillDowncanbeusedatintervalsgreaterthanonecolumn(E.g.Range("B1:C2").FillDownwillreplicatetheformulasofB1andC1toB2andC2respectively). VBAEntireRowandEntireColumn YoucanselectoneormultiplerowsorcolumnswithVBA. Range("B2").EntireRow.Select Range("C3:D3").EntireColumn.Select TheselectionwillalwaysrefertothelastcommandexecutedwithSelect. ToinsertarowusetheInsertmethod. Range("A7").EntireRow.Insert 'Inthiscase,thecontentoftheseventhrowwillbeshifteddownward TodeletearowusetheDeletemethod. Range("A7").EntireRow.Delete 'Inthiscase,thecontentoftheeighthrowwillbemovedtotheseventh VBARowsandColumns JustlikewiththeEntireRowandEntireColumnproperty,youcanuseRowsandColumnstoselectaroworcolumn. Columns(5).Select Rows(3).Select Tohiderows: Range("A1:C3").Rows.Hidden=True Intheaboveexample,rows1to3oftheworksheetwerehidden. VBARowandColumn RowandColumnarepropertiesthatareoftenusedtoobtainthenumericaladdressofthefirstroworfirstcolumnofaselectionoraspecificcell. Range("A3:H30").Row'Referringtotherow;returns3 Range("B3").Column'Referringtothecolumn;returns2 TheresultsofRowandColumnareoftenusedinloopsorresizing. ConsolidatingYourLearning SuggestedExercise AscendingOrder Previouspage Nextpage reporterror/feedback FIRSTSTEPS VBAExcel'sVBA VBAConfiguring VBAVBEEditor VBATheMacroRecorder VBAFirstMacro VBAComments VBAButtonswithMacro FUNDAMENTALS VBAVariables VBAOperators VBAIfThen...Else VBASelectCase VBAFor...Next VBADoLoop VBASubAndFunction VBAStrings VBAEvents VBAArray VBAForEach RANGE VBARange VBANamedRange VBASelect,Row,Column VBALastrow VBAResize VBAExcelFormulas USERFORM VBAUserForm VBAUserFormLayout VBAUserFormEvent VBAWorksheetInterface DEEPENING VBAErrorHandling VBALibraries VBATextFiles VBAPivotTable VBAEmail VBAScheduledExec. SuperExcelVBA.comislearningwebsite.Examplesmightbesimplifiedtoimprovereadingandbasicunderstanding.Tutorials,references,andexamplesareconstantlyreviewedtoavoiderrors,butwecannotwarrantfullcorrectnessofallcontent.AllRightsReserved. Excel®isaregisteredtrademarkoftheMicrosoftCorporation. ©2022SuperExcelVBA|ABOUT × ReportError/Feedback Emailaddress Telluswhat'sgoingon. Send × Thankyou! Thankyouforcontributing.Amessagewassentreportingyourcomment. Close