Now filter for 0 in column B as it signifies the alternate rows (every second row) and press Shift+Space bar to select the entire row and delete them. =MOD(ROW(A2),3).ĭrag the formula till where the data is available in column A.
#How to delete all rows below in excel mod
MOD function gives the remainder after a number is divided by a divisor and ROW function gives the row no of the cell.Įnter the formula =MOD(ROW(A2),2) in the cell B2 ,the first argument to MOD is the row number and second argument is 2 because we want to remove every alternate rows, if we wanted to remove every third row, we would have provided 3 as second argument to MOD function i.e. We will see a simple example to understand that how we could do it with the data below. We could use the MOD and ROW function in excel to remove every alternate rows or every nth row in excel. One way or the other, select the entire control column.Let’s see how we can remove each alternate rows in excel. If the column of data is short, press PageUp if the column is long, it probably would be easier to press Ctrl+Home (with the Shift key still down) and then click on the top cell of the control column with your mouse. Once you select the bottom cell of the column, hold down the Shift key and then move to the top cell with data in your control column. You might need to press Home to move all the way to the left, or press Alt+PageUp to page to the left until you get to the bottom cell of your control column. So, to go to the bottom-right cell of your data, press Ctrl+End. Ctrl+Period - Move clockwise to the next corner of a multi-cell selection.Now hit CTRL+SHIFT+SPACE to select the entire row. Select the first empty cell after the used range and use the shortcut CTRL+SHIFT+DOWN key to select the entire column below the used range.
Ctrl+Backspace - Display the active cell. So to delete rows at the bottom of the sheet, so that they dont appear on the sheet, we hide them.If you hold down the Shift key with any of these combinations, you’ll extend the selection.Īlso, for getting around a multi-cell selection: Alt+PageUp - Move one page to the left.Alt+PageDown - Move one page to the right.Ctrl+Arrow Key - Move the active cell in the direction of the arrow key to the next cell that contains data and that borders an empty cell.End, Enter - Select the right-most cell in the used range in the current row of your spreadsheet.Home - Select the left-most cell in your spreadsheet in the active row.Ctrl+Home - Select the top-top left cell in your spreadsheet.
#How to delete all rows below in excel code
Then copy and paste the following VBA code into the. In the Microsoft Visual Basic for Applications window, click Insert > Module. This will open the Document Inspector dialog box. In the options on the left, click on ‘Info’. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window. Below are the steps to delete all the hidden rows and columns from the workbook in Excel: Click the File option.