![how to delete a continuous row underline in excel how to delete a continuous row underline in excel](https://s33046.pcdn.co/wp-content/uploads/2020/03/problem-sheet.png)
It then uses the ‘For Next loop’ to go through all the cells in Column 2. This will make sure the loop is run only these many times.
How to delete a continuous row underline in excel code#
The above code first counts the total number of rows in the selection. You can also use a simple VBA code to go through each cell in the selected range and delete all the rows where a cell contains a specific text or value.įor example, suppose you have a dataset and I want to delete all cells that have the text Printer in column 2 of the selection.īelow is the code that will do this: Sub DeleteRowswithSpecificValue()įor i = To 1 Step -1 So if anyone cell is empty in a row, this would still delete the entire row. Note: This method selects cells that are blank and don’t check whether the entire row is blank or not. Once these blank cells are identified using SpecialCell, these are then deleted using the EntireRow.Delete method. This is the same method that also allows us to use ‘Go To Special’ dialog box to select all blank cells. The above code uses the SpecialCells property to select and delete all the cells that are blank. Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
![how to delete a continuous row underline in excel how to delete a continuous row underline in excel](https://www.officetooltips.com/images/tips/179_2016/2.png)
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.
![how to delete a continuous row underline in excel how to delete a continuous row underline in excel](https://cdn.extendoffice.com/images/stories/doc-excel/border-horizontal-only/doc-border-horizontal-only-2.png)
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).