VBA Macro to Delete Rows Based on Cell Values
HTML-код
- Опубликовано: 22 июл 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I explain how to use VBA to delete rows based on cell values or conditions. Download the Excel file that contains the sample code: www.excelcampus.com/vba/delet...
This is a simple two step process to apply a filter to a range or Table, then delete the visible rows. In the video I explain the VBA code to automate the process with a macro.
I also share more a few advanced macros that:
1. Display a message box with the number of rows to be deleted.
2. Apply filters to multiple columns to delete cells based on multiple conditions or criteria.
To filter the range or Table we use the AutoFilter method in VBA.
We then use the SpecialCells method to select the visible cells and delete the entire rows from the range.
***Free webinar on Macros & VBA***
If you're new to macros and/or want to learn more, join me for a free upcoming webinar on "The 7 Steps to Getting Started with VBA".
You will learn how to write your first macro and go beyond the limitations of the macro recorder.
👉Click here to register: www.excelcampus.com/macros-we...
****************************************************************
Here are some links and resources:
Articles on the AutoFilter method in VBA: www.excelcampus.com/vba/macro...
Video on how to copy & paste visible cells only: • Copy & Paste Visible C...
Delete entire blank rows: • How to Delete Blank Ro...
Copy and paste to another workbook with VBA: • VBA Macro to Copy Data...
Add a Yes/No message box before a macro runs: • How to Add a Yes No Po...
00:00 Introduction
00:10 VBA Macro to Delete Rows Based on Cell Values
01:01 The Process Explained
05:43 The VBA Macro Code
Thanks for describing the possibility of deleting rows with VBA so precisely
Great video ! Working absolutely fine , thanks a ton !
Another awesome, well-explained video. Thank you Jon.
Thanks so much Tanner! :-)
@@ExcelCampus Well-deserved. Have you thought about adding how to preserve the deleted rows by pasting them to a new worksheet?
New favorite channel!!!
Thank you. Very helpful. If you can provide versions of this for different scenarios (eg, already active sheets as well as new sheets), this would be helpful. Great video.
This was a life safer! Thank you!!
Outstanding code. Thank you sir.
This is excellent Jon. Thank you. Do you how I can use vba (without the autofilter) to delete rows that have a cell in column A with a blank OR a date (any date in it)? Thank you.
Thanks for such good video.
Awesome Jon.
Thank you, this is so much easier than trying to write a loop. How about a scenario where a column contains rows with either text or numbers and delete only those where the cell contains text only?
Great video!!!
Hi Jon, I would like the field to apply to multiple columns, do I simple add Field:4,5,6 for example?
Thank you very much, very useful video
Awesome. Thanks!
Thank you Dina! :-)
Thank you👍
Your lessons are fantastic. Do you know how to edit the code to delete the entire row if the row contains a cell in column B with a strike through please?
Simply Superb. Very useful for me. Appreciate it :-)
Thank you! :)
Another great video Jon! Thanks so much for the help! Keep the lessons coming please!! :). I'm curious- why you didn't use Current Region rather than cell values when defining the range. Thanks!
I answered my own question. when using current region, and running the filter, my header row was deleted along with my filtered data. Looking at Jon's example, I see he moved down 1 row when running the delete versus setting the filter. Can't do that with current region. :) Thanks again for another great video Jon!
Great video! thanks a lot or that!
Question - Say I want to run a “does not begin with” filter and then delete all those rows. How do you go about doing that? Thanks!
Thank you so much
Your Very Good !! :) Thank You from SO !! :)
very useful sir 👍👍👍👍👍👍
Thanks
Thanks ... Thanks
Can it delete rows that meet IF logic? E.g if column C date is less than column G date, then delete.
Is there a way to prevent the macro from going into debug mode when no target values are present? Say I am targeting blanks and there isn't any blanks. If the button is hit it goes into debug mode.
How do I apply AutoFilter for all rows with dates prior to today's date in VBA, then delete the rows?
🎉 Genius
Hi Jon, great video. I have a question. I have a spreadsheet with about 9,000 people on it that consist of male, females and their addresses. I need to delete only the males from this spreadsheet. How do I create a formula for that.
What if No Data "Product 2" in the selected range to delete! will macro stop? Or it will proceed with next step. How to record macro for this check?
Taking advantage of the availability of help, I am having difficulties in this topic:
Any delete in "PPP" product, on main data in table(sheet1), the same must be happen in the sheet table of the product "PPP" on sheet2. The ID of the rows are different in each tables. How to to do that?
Have you any video to solution that?
Thank you Mr jon
First of, great video Jon, you show many scenarios to apply vba and delete data. I 've a question. My table has data like CodeSaler,Name, Start Date (yymmdd hhmmss), Final Date (yymmdd hhmmss), Total time duration. How can I delete all repeat name according the conditions ie: the date (take in main that the column date are aplied for a month), and considert the total duration time, but only if the total duration row has the mayor time betwwen all repeats name that is necesary apply the VBA?
sir can you tell us if the criteria is not met what should do? for eg. i want this filter for all months but if there is no blanks in some scenarios?
Works great, but my excel keeps crashing at the end of the code before clearing the filter. Any idea why that might happen?
I'm doing this, but I'm getting a "runtime error '9', subscript out of range" in reference to the ws = ThisWorkbook.Worksheets("Regular Range"). Any idea what the issue could be? Could it be linked to the fact that the worksheet I'm trying to use this for has more than the usual A-Z columns, and goes into AB,AC,AD, etc?
Please enter the correct sheet name. Regular range is the sheet name of this youtuber might not be yours
If i wanted to use two cells side by side as a simple calculator I.e put the number 3 in the left cell and it gets added to the right cell while also removing the 3 so the cell can be used again, how would one do that?
Hi., i have multiple rows with values and empty values after the = (equal sign). row values similar to _inst__rotz_units = , _inst__rotz_label = RotZ, _inst__y_access = etc., How to delete the rows, which has empty value after equal sign,
subscript out of range before it starts, on the set ws line
I change the "text" to the sheet number and it worked for me.
what if you want to find duplicate names and delete the duplicate row for a specific name, then move to the next set of duplicate names.
I get the 1004 error for '1. line of code stating, "This can't be applied to the selected range. Select a single cell in a range and try again."
Hi Jon.. nice video on automating the deletion of rows based on cell values. I had not thought of using AutoFilter which is a great solution. As you know, I am a member of your VBA Pro course and so I got right to work thinking of a solution of my own. The trick I devised was to count from the bottom up, so you don't have to trap for skipping rows if deleting from the top down. The loop also ends when it hits the row header which makes it easy to adapt to different columns. Also, it works the same on a standard range and a formal Excel Table. The code follows below. As you can see, I am studying and learning your methods. Nice to have a new option using AutoFilter. I'll experiment with that too. Thanks again and Thumbs up!
Sub DeleteProductRowsWithBlanks()
Dim lRow As Long 'variable for last row
Dim cRow As Long 'variable for current row
Dim Counter As Long 'variable for counter of blank cells
lRow = Cells(Rows.Count, 2).End(xlUp).Row 'get count of last row
cRow = lRow 'set current row variable as last row - i.e. count from bottom up
Counter = 0 'set blank cell counter to 0
Application.ScreenUpdating = False 'disable screen updating for faster operation
Do Until Cells(cRow, 5).Value = "Product" 'loop until reaching the column header of Product
If Cells(cRow, 5).Value = "" Then 'test if current row in column 5 is blank
Rows(cRow).EntireRow.Delete 'if blank, then delete entire row
Counter = Counter + 1 'advance blank cell counter by 1
End If
cRow = cRow - 1 'move current row up by 1
Loop 'loop and test again
Beep 'when finished, alert user with BEEP and message box giving the number of deleted rows
MsgBox "Procedure complete - " & Counter & " rows were deleted"
Application.ScreenUpdating = True
End Sub
Hey Wayne,
Thanks for sharing this alternate solution. You are correct with going backwards in the loop to delete rows. We cover that in the course as well, for anyone else that is wondering. If you loop forwards then the macro will likely encounter an error and/or delete rows at the end of the range, which might cause other problems.
With the autofilter method presented in this video we don't have to worry about any of that. However, like Wayne mentioned, it's good to alternate techniques. The autofilter method works great for rows, but does not work for deleting columns based on conditions.
Thanks again Wayne! I really appreciate your support! :-)
Hi Jon.. thanks.. with your help (VBA Pro Course, your videos, your web site, etc.), I'm glad I now have the ability to contribute. And.. good points on the use of AutoFilter bypassing some of the issues involved with direct manipulation. There are so many ways to approach a problem. VBA unlocks the door to them all! In that regard, I've been experimenting with the use of Arrays and came up with the following for your example which would likely process faster on a very large data set (see revised code below). Thanks again for all the great resources you offer. Thumbs up!
Sub DeleteProductRowsWithBlanksUsingAnArray()
Dim arrProducts() As Variant
Dim i As Long
Dim c As Long
arrProducts = Range("E1:E" & Cells(Rows.Count, 2).End(xlUp).Row)
i = UBound(arrProducts)
c = 0
For i = UBound(arrProducts) To 4 Step -1
If arrProducts(i, 1) = "" Then
Cells(i, 5).EntireRow.Delete
c = c + 1
End If
Next i
Beep
MsgBox "Procedure complete - " & c & " rows were deleted"
End Sub
Running the macro when the column does not contain the lookup criteria an error on the delete rows section appears. How do you bypass this?
I am looking to have a macro button and when the button is pushed a user input box appears and then asks you what row on the excel sheet is to be deleted. Of course the workbook will have protection on so it will have to be turned off before the action happens and then restore after.. thanks Jon
Hello Brother, am getting debug on "Regular Range" Line no 3.. Can you plz help me to fix it
what if i have multiple criteria to delete , like if the status is closed, resolved, active, etc.?
Hi. Thank you for the video. I have criteria that lists several (22) tests in the test column. I want to delete everything but Spanish. Can I use "Spanish"? Or how would I list that in the criteria? It is a table.
a bit late and not the video poster, but yes you can use Criteria1:="Spanish"!
How would I use VBA to Clear a range based on cell value, i.e If they use dropdown to display "Del" in a cell (B1) from that being changed I want the Range C1:Z1 to clear contents, Thanks
is it possible to delete one cell (not entire row )based on cell values ?
Hi Jon great video - I need to run the reverse macro meaning...i want to delete all rows NOT Equal to a "Product 2" text name in the column. I would like to delete all rows that do NOT contain the name Product 2. I have tried using the in place of = in the macro but I get this error...."Compile error, expected named parameter." When I try to record a macro using filter to select all but name, I need I get another error called "too many line continuations." My tables are over 6000 lines long..Any ideas?
hi abbisandi. were you ever able to solve this? i would love to help!
@@greggowaffles Criteria1:="=*text*"
I have a spreadsheet with almost more than 1000000 rows of data. I want to delete every 600th row so for eg i will start with starting from 1 - keep that data and delete the rest from 2 to 600. Next I will keep 601 and delete up to 1200. so basically increment of 600. What code should i use and how to delete?
Jon - additionally - how would I get this macro to run on multiple sheets in a workbook but not ALL of the sheets. I would have to name the sheets in the code? What would the code look like?
I don't mean to steal Jon's platform, but to help answer your question, you would have to loop all the worksheets in the workbook to exclude the worksheets you don't want the code to run on.
bump, as I also need to know this. 3 months with no reply/answer?
@@alexhoward864 What does your worksheet look like?
How do you prevent this method from always deleting the first row, which the Auto Filter will never hide?
There is a problem, for example: if you re-run the code again (value bases on table), an error will pop up!!!
If we want to find some content and delete .. if we don't know exact content
Attempting to delete rows with the word "Total" in a cell.
Can we use it in Google sheets
These VBA macros are based on Microsoft Excel. Google Sheets has its own coding language called App Scripts where you could do something similar.
These VBA macros are based on Microsoft Excel. Google Sheets has its own coding language called App Scripts where you could do something similar.
And what if data exceeds row G1000 bro? Please always show something which gives permanent solutions and not temporary ones
Instead of using "" or "12/3/14", ... How can I do this using today's date? Example: Delete all rows that are older than today or yesterday.
Frank did you ever figure this out? If I have rows in weekly buckets and I update the spread sheet, I want the macro to delete the rows that are older than the current date I add, how would I do that?
I think we should clear filter before using auto filter for better result.
Great point Hoang! I'll update the post and macro. Thanks! :-)
subscript out of range