#ADVANCED SELECT IN EXCEL TO DELETE CERTAIN ROWS IN EXCEL CODE#
The above code uses the SpecialCells property to select and delete all the cells that are blank. Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete You can also use the EntireRow.Delete method to delete all blank rows.īelow is the VBA code that will select blank cells in the selected dataset and delete the entire row. Within the VBA loop, I have used the Selection.Rows(i).EntireRow.Delete method to delete every alternate row. In case you want to delete every third row, you can use -3. It’s important to run this from the last row in the selection to the first as we don’t want the row numbers to change when a row is deleted.Īlso, Step -2 is used since we need to delete every other row (from bottom to top). For example, if there are 12 rows, this loop will run from 12 to 1 (i.e., 12 times).
Then I have used a For Next loop to run this as many times as many rows are there. This is the type of scenario where VBA really shines.īelow is the VBA code that will go through all the rows in the selection and delete every second row: Sub DeleteAlternateRows()įirst, I have used a variable RCount to get the total number of rows in the selection. I used to work with financial data where every second row was empty and had to be deleted. Sometimes, you may get a data dump where every second row (or third, fourth or Nth rows) is useless and needs to be deleted. Delete Alternate rows (or Delete Every Third/Fourth/Nth Row) The above code applies to the EntireRow.Delete method to the entire selection. In case you want to delete all the rows in a selected range of cells, you can use the VBA macro code below: Sub DeleteEntireRow() For example, in case you start at the top and delete row 1 first, all the rows below it would be shifted one row up and the numbering would be off (as row 5 would become row 4 and so on) Delete All Rows in the Selection IMPORTANT: When you’re deleting rows with something similar to the above code, remember to start deleting from the bottom and then go up. The above code uses the same logic, where it specifies the row numbers and Excel will delete these rows one by one. You can also delete multiple rows by specifying these rows in the code.įor example, the below code will delete row number 1, 5 and 9 in the worksheet: Sub DeleteEntireRow() The above code first specifies the row that needs to be deleted (which is done by specifying the number in bracket) and then uses the EntireRow.Delete method to delete it. To delete an entire row in Excel using VBA, you need to use the EntireRow.Delete method.įor example, if you want to delete the entire first row in a worksheet, you can use the below code: Sub DeleteEntireRow() Delete Alternate rows (or Delete Every Third/Fourth/Nth Row).
0 kommentar(er)
