how to delete certain rows in excel based on color

How to delete certain rows in excel based on color how to#

Excel 2003: Inserting and Deleting Rows and Columns.

If rng.Cells(i).Value = 'x' Then rng.Cells(i).EntireRow.Select Turn off filter from the Data tab. 'If cell i in the range contains an 'x', delete the entire row. This is an alternate method to the method that is shown above. The results of this macro in all versions of Excel are as follows: A1: a B1: 1Īdditional method for using a loop to delete cells 'Loop for a count of 1 to the number of rows Use the following macro when you want to use a loop to delete cells: Sub DeleteCells2() Recommended method for using a loop to delete cells However, unlike the behavior of the loop in Excel 2002 and in later versions of Excel, when the 'For Each.Next' loop evaluates a cell in Excel 5.0 and in Excel 7.0, the loop reevaluates the cell if it is deleted in the loop. Then, cell A3 assumes the contents of cell A4, cell A4 assumes the contents of cell A5, and so on. When row 3 is deleted, all cells move up one row. When you run the DeleteCells macro in Excel 5.0 and in Excel 7.0, the macro deletes all rows that contain an 'x.' The results of the macro are as follows: A1: a B1: 1 Behavior of the sample macro in Microsoft Excel 5.0 and Microsoft Excel 7.0 After the For Each.Next loop evaluates a cell, it evaluates the next cell therefore, when cells are shifted, they may be skipped by the loop. For example, cell A3 assumes the contents of cell A4, cell A4 assumes the contents of cell A5, and so forth. When Microsoft Excel deletes row 3, all cells move up one row. The results of the macro are as follows: A1: a B1: 1 Although rows 4 and 9 contain an 'x' in column A, the macro does not delete the rows. When you run the DeleteCells macro in Excel 2002 and in later versions of Excel, only rows 3, 6 and 8 are deleted. 'Loop through cells A1:A10 and delete cells that contain an 'x.'īehavior of the sample macro in Excel 2002 and in later versions of Excel In a new macro module, type the following macro. To use the macro in this article, type the following sample data in a worksheet: A1: a B1: 1 However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Microsoft support engineers can help explain the functionality of a particular procedure. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. Microsoft provides programming examples for illustration only, without warranty either expressed or implied.

How to delete certain rows in excel based on color how to#

This article describes the differences, and it provides a Visual Basic for Applications macro example that illustrates how to delete cells in a loop. Microsoft Excel 2002 and later versions of Excel delete cells in a 'For Each.Next' loop in a different way than Microsoft Excel 97 and earlier versions of Excel do. For more information about this change, read this blog post. Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise.