Cập nhật nội dung chi tiết về How To Delete Entire Row In Excel Using Vba (Examples) mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Adding and deleting rows is part of everyday common tasks when working with Excel.
While you can do this easily from the worksheet itself, sometimes you may want to use the VBA route to delete rows in Excel. These could be deleting a specific row, multiple rows in the selection, deleting alternate rows or those that have a specific value in it.
In this tutorial, I will show you how to delete rows in Excel using VBA (multiple scenarios).
So let’s get started!
Delete an Entire Row using VBA
To delete an entire row in Excel using VBA, you need to use the EntireRow.Delete method.
For example, if you want to delete the entire first row in a worksheet, you can use the below code:
Sub DeleteEntireRow() Rows(1).EntireRow.Delete End SubThe 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.
You can also delete multiple rows by specifying these rows in the code.
For example, the below code will delete row number 1, 5 and 9 in the worksheet:
Sub DeleteEntireRow() Rows(9).EntireRow.Delete Rows(5).EntireRow.Delete Rows(1).EntireRow.Delete End SubThe above code uses the same logic, where it specifies the row numbers and Excel will delete these rows one by one.
IMPORTANT: When you’re deleting rows with something similar to the above code, remember to start deleting from the bottom and then go up. 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
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() Selection.EntireRow.Delete End SubThe above code applies to the EntireRow.Delete method to the entire selection.
Delete Alternate rows (or Delete Every Third/Fourth/Nth Row)
Sometimes, you may get a data dump where every second row (or third, fourth or Nth rows) is useless and needs to be deleted.
I used to work with financial data where every second row was empty and had to be deleted.
This is the type of scenario where VBA really shines.
Below is the VBA code that will go through all the rows in the selection and delete every second row:
Sub DeleteAlternateRows() RCount = Selection.Rows.Count For i = RCount To 1 Step -2 Selection.Rows(i).EntireRow.Delete Next i End SubLet me explain how this VBA code works.
First, I have used a variable RCount to get the total number of rows in the selection.
Then I have used a For Next loop to run this as many times as many rows are there. For example, if there are 12 rows, this loop will run from 12 to 1 (i.e., 12 times). 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.
Also, Step -2 is used since we need to delete every other row (from bottom to top). In case you want to delete every third row, you can use -3.
Within the VBA loop, I have used the Selection.Rows(i).EntireRow.Delete method to delete every alternate row.
Delete Blank Rows with VBA
You can also use the EntireRow.Delete method to delete all blank rows.
Below is the VBA code that will select blank cells in the selected dataset and delete the entire row.
Sub DeleteBlankRows() Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete End SubThe above code uses the SpecialCells property to select and delete all the cells that are blank. This is the same method that also allows us to use ‘Go To Special’ dialog box to select all blank cells.
Once these blank cells are identified using SpecialCell, these are then deleted using the EntireRow.Delete method.
Note: This method selects cells that are blank and don’t check whether the entire row is blank or not. So if anyone cell is empty in a row, this would still delete the entire row.
Delete Rows with a Specific Word/Value
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.
For example, suppose you have a dataset and I want to delete all cells that have the text Printer in column 2 of the selection.
Below is the code that will do this:
Sub DeleteRowswithSpecificValue() For i = Selection.Rows.Count To 1 Step -1 If Cells(i, 2).Value = "Printer" Then Cells(i, 2).EntireRow.Delete End If Next i End SubThe above code first counts the total number of rows in the selection. This will make sure the loop is run only these many times. It then uses the ‘For Next loop’ to go through all the cells in Column 2.
The IF THEN ELSE statement is then used to check the value in each cell in column 2. And in case the value/text matches the specified text (which is ‘Printer’ in this example).
In this example, I have checked whether the text matches a specific string or not. You can also do this with values. For example, you can delete all rows where the sale value is less than 1000 or more than 1000.
Note: An important thing to note here is that the loop runs from Selection.Rows.Count To 1 to make sure when a row is deleted, it doesn’t impact the rows above it.
How to Use This VBA Code
Now let me show you how to use all the codes mentioned in this tutorial to delete the entire row.
You need to copy and paste these codes in a module in Excel VB Editor. Once you have these codes copied, you can then run the macro codes.
Below are the steps to copy and paste these VBA codes in a module:
Hold the ALT key and press the F11 key (or Function + Option + F11 in Mac). This will open the VB Editor
Copy and Paste the above codes in the module.
I have also written a detailed tutorial on different ways to run VBA macro codes in Excel.
So these were some VBA codes that you can use to delete entire rows in Excel (in different scenarios). The same logic can also be applied in case you want to delete columns instead of rows (with the corresponding adjustment in the code examples).
Hope you found this tutorial useful!
You may also like the following Excel tutorials:
How To Delete A Page In Excel
What to Know
Go to the View tab, select Page Break Preview, then drag the dotted blue line to adjust the area you want to print.
If you only want to print part of the worksheet, highlight the area you want to print, then go to the File tab and select Print.
To set a print area for the document permanently, go to the Page Layout tab, highlight the area you want to print, then select Print Area.
This article explains how to delete pages in Excel. Instructions apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.
How to Delete Unwanted Pages in Excel
Page breaks are the boundaries in a worksheet that decide what content goes on your printed document page. Excel chooses these for you automatically, using your default paper size and margin settings. You can also adjust the automatic page breaks by scaling your print job smaller (less than 100 percent) or larger (more than 100 percent) than your working document.
Insert, delete, or move page breaks in Excel to make sure pages print as expected.
Open the worksheet in which you want to delete a page and select the View tab.
Select Page Break Preview in the Workbook Views group.
You can adjust page breaks in the Normal view in Excel, but it’s easier to use Page Break Preview to work on the page break layout. The preview mode shows how any change you make to columns or rows impacts the automatic page breaks.
With Page Break Preview enabled, you can see a dotted line representing the automatic page break, with each page numbered.
You can select any of the blue lines (both dotted and solid) to adjust the print areas.
Select and drag the dotted blue line (an automated print break) to adjust the area you would like to print. The line turns solid, converting it to a manual page break.
When you finish adjusting the page breaks, select Normal in the Workbook Views group.
How to Set Your Print Area in Excel
Creating page breaks is a great way to manage larger documents, but what if you want to print a snapshot of the content and not the whole worksheet? You can use printer options to print a selected area by following these steps.
For one-time printing:
Select and drag to highlight the area of the worksheet that you want to print.
Select the File tab.
Choose Print.
Choose Print Selection in the list under Settings.
Change Print Settings Permanently
If you’ll print the selected area more than once and want to set a print area for the document permanently, you can do it this way.
Go to the Page Layout tab.
Highlight the area that you want to print, then select Print Area in the Page Setup group.
Choose Set Print Area.
Delete Or Insert Rows Based On Cell Value
This tutorial will demonstrate how to delete or insert rows based on cell values.
Delete Row Based on Cell Value
This will loop through a range, and delete rows if column A says “delete”.
Sub DeleteRowsBasedonCellValue() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 If .Range("A" & Row).Value = "delete" Then .Range("A" & Row).EntireRow.Delete End If Next Row End With End SubWe must start the loop with the bottom row because deleting a row will shift the data, skipping rows if you loop top to bottom.
Also, notice that instead of manually entering in the last row, we calculate the last used row.
Delete Row – Based on Filter
In the previous example, we looped through the rows, deleting each row that meets the criteria. Alternatively, we can use Excel’s AutoFilter to filter rows based on some criteria and then delete the visible rows:
Sub FilterAndDeleteRows() 'Declare ws variable Dim ws As Worksheet Set ws = ActiveSheet 'Reset Existing Filters On Error Resume Next ws.ShowAllData On Error GoTo 0 'Apply Filter ws.Range("a1:d100").AutoFilter Field:=1, Criteria1:="delete" 'Delete Rows Application.DisplayAlerts = False ws.Range("a1:d100").SpecialCells(xlCellTypeVisible).Delete Application.DisplayAlerts = True 'Clear Filter On Error Resume Next ws.ShowAllData On Error GoTo 0 End SubDelete Row Based on Cell Criteria
This will loop through a range, deleting rows if the cell in column A meets certain criteria (< 0):
Sub DeleteRowsBasedonCellValue() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 If .Range("A" & Row).Value < 0 Then .Range("A" & Row).EntireRow.Delete End If Next Row End With End SubDelete Row if Cell is Blank
This will loop through a range, deleting a row if a cell in column A is blank:
Sub DeleteRowsBasedonCellValue() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 If .Range("A" & Row).Value = "" Then .Range("A" & Row).EntireRow.Delete End If Next Row End With End SubDelete Blank Row
Sub DeleteBlankRows() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 If WorksheetFunction.CountA(.Rows(Row)) = 0 Then .Rows(Row).EntireRow.Delete End If Next Row End With End SubDelete Row if Cell Contains Value
This will loop through a range, deleting a row if the cell in column A is not blank:
Sub DeleteRowsBasedonCellValue() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 .Range("A" & Row).EntireRow.Delete End If Next Row End With End SubInsert Row Based on Cell Value
This will loop through a range, inserting rows if a certain cell in that row says “insert”:
Sub InsertRowsBasedonCellValue() 'Declare Variables Dim LastRow As Long, FirstRow As Long Dim Row As Long With ActiveSheet 'Define First and Last Rows FirstRow = 1 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Loop Through Rows (Bottom to Top) For Row = LastRow To FirstRow Step -1 If .Range("A" & Row).Value = "insert" Then .Range("A" & Row).EntireRow.Insert End If Next Row End With End SubHow To Delete A Pivot Table In Excel
Delete a Pivot Table in a Microsoft Excel Workbook
Applies to: Microsoft ® Excel ® 2010, 2013, 2016, 2019 and 365 (Windows)
A pivot table can be deleted in an Excel workbook in several ways. You can delete a pivot table, convert a pivot table to values or clear data and customizations from a pivot table to reset it. When a pivot table is created from source data in a workbook, Excel creates a pivot cache in the background. If you delete a pivot table or a source worksheet with the original data, Excel still retains the cache.
Recommended article: 10 Great Excel Pivot Table Shortcuts
Deleting a pivot table
To delete a pivot table:
Select a cell in the pivot table.
Press Delete.
Below is the Select All command in the Ribbon:
You can also delete a pivot table by deleting the worksheet on which it appears (assuming there is no other data on the sheet) or by deleting all of the rows on which the pivot table appears.
Deleting a pivot table and converting it to values
You can delete a pivot table and convert it to values. This can be useful if you want to share the pivot table summary information with clients or colleagues.
To delete a pivot table and convert it to values:
Select a cell in the pivot table.
Below is the Paste drop-down menu in Excel:
Deleting pivot table filters, labels, values and formatting
You also have the option of resetting a pivot table by deleting pivot table filters, labels, values and formatting but retaining the pivot table.
To delete pivot table data:
Select a cell in the pivot table.
Add or remove fields in the Pivot Table Fields task pane.
Below is the Clear All command in the Ribbon in Excel:
If pivot tables are sharing a data connection or if you are using the same data between two or more pivot tables, then if you select Clear All for one pivot table, you could also remove the grouping, calculated fields or items and custom items in shared pivot tables. A dialog box should appear if Excel is going to remove items in shared pivot tables and you can cancel the operation.
Did you find this article helpful? If you would like to receive new articles, join our email list.
More resources
How to Remove Blanks in a Pivot Table in Excel (6 Ways) How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways) How to Convert Seconds to Minutes and Seconds in Excel Worksheets
Related courses
Bạn đang đọc nội dung bài viết How To Delete Entire Row In Excel Using Vba (Examples) trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!