How to delete certain rows in excel at once code#
Once we run the code we can see that the row which had the blank cells has been deleted. SpecialCells is a function in VBA which returns us all the cells which match our conditions and our condition was the blank cells in that range so we used an xlCellTypeBlanks function. Range("A1:D5").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Step 3: Write the following code to delete the rows which have blank cells. Step 2: Declare the subfunction in the code window to start writing the code, Step 1: From the developer’s Tab click on Visual Basic to open a visual basic editor. Our goal is to find that blank row and delete it from the data.įollow below steps to delete row in excel using VBA. One of the employees was on leave and couldn’t do sales for three months so the cells are empty for him. Now we have data for employees and their sales are done in three months Jan, Feb, and March. Note: I will Insert back the data in the excel sample sheet for demonstration. Once we run the code we see the following result, The entire data has been deleted means the first five rows have been deleted as we had data in the first five rows. Step 4: Run this code by hitting F5 or Run button and see the output. Step 3: Write the following code shown below to delete the entire five rows. Step 2: In the code declare a sub-function to start writing the code, Step 1: In the developer’s tab click on Visual Basic to open VBA Editor. Follow below steps to delete row in excel using VBA. I want to delete all the first five rows. For demonstration purpose, I have the following data as shown below,
But what about if we have to delete multiple rows. VBA Delete Row – Example #2Įarlier in the first example, I have deleted only a single row. Once we run the code we can see that the values from both cell A1 and B1 are removed as the entire first row has been deleted.
Step 4: Run this code by hitting F5 or Run button and see the output. Step 3: Now write the following code to delete the row. Step 2: In the code segment declare a sub-function to start writing the code. Step 1: Go to the developer’s Tab click on Visual Basic to open VBA Editor. To run the macro, point to Macro on the Tools menu, and then click Macros.Note: To use Excel VBA we must have developer tab enabled from Files tab in the options section.įollow the below steps to delete row in excel using VBA. Switch to the worksheet that contains the data, and then select the range A1:A9. ' If Y is True, make it False if Y is False, make it True. increment I by one so we can cycle through range. ' Loop once for every row in the selection. Y = False ' Change this to True if you want to In the new module, type the following macro: Note In Microsoft Office Excel 2007 and later versions, click Visual Basic in the Code group on the Developer tab.
On the Tools menu, point to Macro, and then click Visual Basic Editor. In a new worksheet, in the range A1:A9, type the numbers 1 to 5, skipping rows, as in the following example: A1: 1 To use the following macro, set up an Excel workbook as follows: Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 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. This article contains a sample Microsoft Visual Basic for Applications macro that you can use to delete every other row in a selected range on a Microsoft Excel worksheet.
0 kommentar(er)
