#Excel shift cells up how to
Shift: It is used to specify how to shift remaining cells to replace the deleted cells This will perform the operation of deleting the cells by shifting the remaining cells up. Here we have not specified any value so the excel will select all cells with all format and out of those, it will select only those cells that have blank values inside them XlCellTypeSameValidation: Cells with same validation criteria XlCellTypeSameFormatConditions: Cells with same formats XlCellTypeLastCell: Last cell in the used range XlCellTypeFormulas: Cells containing formulas
XlCellTypeConstants: Cells with constants XlCellTypeComments: cells containing comments XlCellTypeAllValidation: Cells having validation All cells of any format to be selected.īut here, you can specify a value on which will depend the kind of data that will be selected Its default value is xlCellTypeAllFormatConditions i.e. Value: This used to specify the type of cells to include and it is optional. Here we have selected type as xlCellTypeBlanks i.e. Type: is the kind of data you want to select, this is mandatory to specify. This is method used by excel, which has a syntax: Expression.SpecialCells(Type,Value) SpecialCells(xlCellTypeBlanks).Select:Īs you have selected a data, the excel recognizes that, then you selected “Go to Special”. Selection.SpecialCells(xlCellTypeBlanks).Selectĭeleteblank()…….is the name given to macro by you in first step ‘ delete blank cells in the data selected
#Excel shift cells up code
Excel will first create a new Module in which the code will be saved. You can also see the code created by excel here. You will see Microsoft Visual Basic window To see the coding that Excel creates, press “ALT + F11” This will create a macro once and for all. Now perform the same steps from 1 to 3 as explained earlier and then stop recording.
GIve the name to macro that you can remember and provide it a shortcut key if you want Recording a Macro however is a better way to perform this as this will create on macro which can be used for any data Now if you want to perform same series of steps for another set of data, normally you need to follow the same procedure. This operation looks easy but it involves 3 different steps. BY default it will have “Shift Cells Up” selected and result will be a data table with all blank cells gone and the remaining cells will be shifted up. Select the option as per your data and press “OK”. Now all the cells that are blank would be selected, Go to “Home” -> Cells (“Delete”) ->”Delete Cells” You can see an option called “Blanks”, select that and enter “OK”ģ. Go to “Home” – > Editing (“Find & Select”) -> “Go to Special” Suppose you have a data table in Excel 2007 and you want to delete all the blank cells from the selected data.Īs you can see, there are certain blank cells in the data, suppose you want to delete them, than you have to follow below stepsĢ.
0 kommentar(er)
