Bạn đang xem bài viết Delete Or Insert Rows Based On Cell Value được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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 SubDelete All Custom Cell Styles Excel
Asked
Is it possible to delete ALL the custom/created cell styles in a workbook ? Just leaving the default styles.
Without having to delete them all one by one
PeterH PeterH
6,412 16 16 gold badges 40 40 silver badges 74 74 bronze badges
Try this small VBA macro:
Sub StyleKiller() Dim N As Long, i As Long With ActiveWorkbook N = .Styles.Count For i = N To 1 Step -1 If Not .Styles(i).BuiltIn Then .Styles(i).Delete Next i End With End SubThis resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
To simply remove all without using INDEX, try the below:
Sub StyleKiller() Dim st As Style On Error Resume Next For Each st In ActiveWorkbook.Styles If Not st.BuiltIn Then st.Delete End If Next On Error GoTo 0 End SubOk, this wasn’t as hard to do as I first thought.
Bit messy as I don’t often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles() Dim MyBook As Workbook Dim tempBook As Workbook Dim CurStyle As Style Set MyBook = ActiveWorkbook On Error Resume Next For Each CurStyle In MyBook.Styles Select Case chúng tôi Case "20% - Accent1", "20% - Accent2", _ "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _ "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _ "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _ "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _ "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _ "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _ "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _ "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _ "Note", "Output", "Percent", "Title", "Total", "Warning Text" Case Else CurStyle.Delete End Select Next CurStyle Set tempBook = chúng tôi Application.DisplayAlerts = False MyBook.Styles.Merge Workbook:=tempBook Application.DisplayAlerts = True tempBook.Close End SubThe issue of custom styles is also discussed in http://support.microsoft.com/kb/213904
I found that installing the free “XLStyles Tool” from MS Store as suggested in the KB article was a very convenient way to delete the custom styles.
Super User works best with JavaScript enabled
How To Delete Entire Row In Excel Using Vba (Examples)
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 Clear Restricted Values In Cells In Excel?
How to clear restricted values in cells in Excel?
Have you ever encountered a prompt box as the left screenshot shown when trying to enter content into a cell? That’s because the cell has been restricted for entering certain value. This article will show you how to clear the restricted values from cells in Excel.
Clear restricted values in cells in ExcelQuickly clear restricted values in cells with Kutools for Excel
Clear restricted values in cells in Excel
Please do as follows to clear restricted values in cells in Excel.
Now you have cleared the restricted value of the selected cell.
Quickly clear restricted values in cells with Kutools for Excel
Here introduce the Clear Data Validation Restrictions utility of Kutools for Excel. With this utility, you can batch clear all data validation restrictions from a selection or multiple selected ranges at the same time.
Before applying Kutools for Excel, please download and install it firstly.
Then all data vaidation restrictions are removed from the selected range(s).
If you want to have a free trial (
Related articles:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse:
Quickly insert
complex formulas, charts
and anything that you have used before;
Encrypt Cells
with password;
Create Mailing List
and send emails…
Super Formula Bar
(easily edit multiple lines of text and formula);
Reading Layout
(easily read and edit large numbers of cells);
Paste to Filtered Range
…
Merge Cells/Rows/Columns
without losing Data; Split Cells Content;
Combine Duplicate Rows/Columns
… Prevent Duplicate Cells;
Compare Ranges
…
Select Duplicate or Unique
Rows;
Select Blank Rows
(all cells are empty);
Super Find and Fuzzy Find
in Many Workbooks; Random Select…
Exact Copy
Multiple Cells without changing formula reference;
Auto Create References
to Multiple Sheets;
Insert Bullets
, Check Boxes and more…
Extract Text
, Add Text, Remove by Position,
Remove Space
; Create and Print Paging Subtotals;
Convert Between Cells Content and Comments
…
Super Filter
(save and apply filter schemes to other sheets);
Advanced Sort
by month/week/day, frequency and more;
Special Filter
by bold, italic…
Combine Workbooks and WorkSheets
; Merge Tables based on key columns;
Split Data into Multiple Sheets
;
Batch Convert xls, xlsx and PDF
…
More than 300 powerful features
. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More… Free Download… Purchase…
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint
, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by
Read More… Free Download… Purchase…
Cập nhật thông tin chi tiết về Delete Or Insert Rows Based On Cell Value trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!