Delete Rows based on Value/Text // Excel VBA Tutorial
HTML-код
- Опубликовано: 15 окт 2024
- In this video I am showing you how to delete rows based on value or text. The code is the following:
Sub delete_rows()
For i = 2 To Sheets(2).UsedRange.Rows.Count
If Cells(i, 3).Value = "No" Then
Rows(i).EntireRow.Delete
End If
If Cells(i, 3).Value = "No" Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub
Hello guys and gals! I am sorry for the black screen at the end, I suppose I messed up something while editing. The video ends at 4:15!
Hello Finance Nik. Thank you for the video. and for the channel. Fantastic content!!
Finally! A simple, concise Excel video with code that's general enough to easily modify. Works perfectly. Thanks.
It is not working when I have more than 3-4 rows in a line to delete
Kia Ora! Thanks for a very helpful video. How would you edit the code to delete cell that have a value after a particular date? Eg. delete dates after 11/06/2101 (dd/mm/yyyy) from a data set
Thank you so much Nik Helped me a lot after a lot of searching in different videos
Data Nik, thank you for the video, it was very helpful. I have a question. Is there a way to do a partial clear of data, in a row up to a certain phrase and be able to keep all the data that follows that point? And, could that code to seek out a phrase that would just target those rows to be cleared? I apologize if I am being redundant...just want to be as clear as possible.
Hi, very nice video.
I have a question:
what if I need to apply this macro for a multiple criteria on the same column "example, i need to cancel all the rows based on the texts "Bread, orange, cake, ".
Also, what if my range is over 250000 lines? Many thnaks
Hello! Please tell me if the GoalSeek macro written in VBA works in Microsoft Excel for Mac and, in particular, for the version for devices running on the Apple Silicon M1 processor. I often use it to calculate additional financing for the cash gap when building financial models due to my profession. I will be very grateful if you make a video with such a macro and check its work, as I heard that in the version of Excel for Mac, there are problems with this macro.
And also it will also be interesting to see if Power query works the same way in Excel for Mac as it does in the Windows version.
Thank you for the content. Very useful videos, on the vastness of RUclips there are few useful videos about using office programs on a Mac
Good evening! Thank you for your input, much appreciated. I do not own an M1 personally (unfortunately youtube doesnt pay yet), however, I can so some research on it. I dont think It makes a difference between processors as VBA is single threaded anyway. I still have one video in the works and then I will check you inqueries out!
Hi Thank you for the video - it was very helpful. I have a question - is there a way to do the same for text file (.txt extension) search 1st word (text) and search 2nd word (text) and in between the range want to remove and save the file - PLEASE advise! Thank you for your help! - Dalpat
Hi Dalpat,
Thanks for leaving a comment! As far as I know, though, Excel is not the place to be when trying to edit text files. However, you can also use VBA in Microsoft Word. It works a little bit different here, but you do also have something like a range function. Try this:
Sub RemoveTextBetweenWords()
Dim filePath As String
Dim inputFile As String
Dim outputFile As String
Dim fileContents As String
Dim firstWord As String
Dim secondWord As String
' Set the file path and names
filePath = "C:\Path\To\Your\File\" ' Change this to your file's directory
inputFile = "input.txt"
outputFile = "output.txt"
' Set the words to search for
firstWord = "FirstWord"
secondWord = "SecondWord"
' Read the contents of the input file
Open filePath & inputFile For Input As #1
fileContents = Input$(LOF(1), #1)
Close #1
' Find the positions of the first and second words
Dim firstPos As Long
Dim secondPos As Long
firstPos = InStr(1, fileContents, firstWord, vbTextCompare)
secondPos = InStr(firstPos, fileContents, secondWord, vbTextCompare)
' Check if both words were found
If firstPos > 0 And secondPos > 0 Then
' Extract the text before the first word
Dim newText As String
newText = Left(fileContents, firstPos - 1)
' Append the text after the second word
newText = newText & Mid(fileContents, secondPos + Len(secondWord))
' Save the modified content to the output file
Open filePath & outputFile For Output As #2
Print #2, newText
Close #2
MsgBox "Text between '" & firstWord & "' and '" & secondWord & "' has been removed."
Else
MsgBox "Either '" & firstWord & "' or '" & secondWord & "' not found in the file."
End If
End Sub
@@FinanceNik
Thank you Data Nik for prompt response. Perfect! I used the idea and works perfectly fine for me. Appreciate it so much for your tips/help/Time/Efforts....Have a great day...
QQ: what if user wants to select Input File by themselves? and after extracting the text save same file but different folder. Any tips Please.
Hi Nik, I am removing lines between 2 searched text string, works perfectly fine but I have these searched string repeated many times, I removed with your suggestion 1 or 2 times but when it went to last search, vba errored out string not found, How do i resoled... i used Do while loop but not working, please advise! thank you for your time!
This is actually awesome! I have a question though. I've been trying to find someone who knows how to do this in excel forever. So I'm in SEO and a common problem I come across whenever consolidating backlink data is going through thousands of URLs and deleting rows with unwanted Top-Level Domains (.surf, .gq, .bid, .tw, etc...). What I've resorted to doing is the eye bleed method of CTRL + F, Search TLD "find all", CTRL+ A, then CTRL + -. Repeat steps for like 50 different unwanted TLDs... My question is if I'm given a list of URLs, is it possible to do a similar process you show in your video but to delete URLs that end with a "list" of specified TLDs, while also deleting the rows associated with them?
Hey RedSpectreNine, the world of Excel is your oyster when it comes to stuff like that. Basically, yes it is entirely possible. Out of the top of my head some suggestions for you:
1. Create a sheet with all the urls (or extensions of such) in a row
2. Create a loop that loops over the sheet with the urls and then loops over the whole sheet of domains. This is called a "nested for loop". So for instance if the first row in your url sheet ends with .surf, then the code will take this cell and compare it with the cells in the sheet of all domains. The rest should be as in the video
3. One problem you might encounter, however, if I have understood you correctly, is that you want to check whether the ending of the url is within another url (at the end). In this case you have to adjust your code to not check the full cell, but if each cell of the all-domain sheet "contains" the url ending.
Summary:
- get to know nested for loops
- get to know how to compare cells that contain a specific string
- the rest should be simmilar as to what I have done in the video.
I hope this helps!
Is there a way to change vba code to let this macro run on whatever sheet you are on instead of the first tab?
sir the font is si small nearly invisible pl enlarge whic could easily be watched dont mind sir
The problem i am having is that the empty rows i have are zero value based on formula.
so , when i ma using the code , its deleting every thing. any advise.
My 1st row is row 8
this is the code i am using
Sub Delete_Rows()
Dim lastrow As Long, xrow As Long
xrow = 8
lastrow = Range("a400").End(xlUp).row
Do Until xrow = lastrow
If Cells(xrow, 8).Value = "" Then
Cells(xrow, 8).Select
Selection.EntireRow.Delete
xrow = xrow - 1
lastrow = lastrow - 1
End If
xrow = xrow + 1
Loop
End Sub
Hi, I am getting type mismatch error when I try to include OR so that I can search and delete multiple names
Thank you ! How do I delete all rows except for Header Rows?
THANK YOU VERY MUCH...
Hi - is there a way to change the range to apply to an entire column rather than the used range? Thanks
Hi Georgina, absolutely. If you want to reference a column - for example columns A - then the reference would be xx.range("A:A").value
@@FinanceNik Yeah but where do you put that at? What do you replace?
@@BambooScardid you figure that out? Have the same problem understanding that
@@TheVeraloove no :(
Hey, I need to do something similar but instead of yes/no, I need to delete rows that contain any date in the "I" column. Could you help?
Hey Michael, yes sure! I would suggest doing a for-loop and in that for loop you have an if statement stating somethting like this: if cells(i,*column nr with the date*).value ' ' then row(i).delete
@@FinanceNik Greetings Nik,
Thank you for the videos. I too need help with a similar situation. Could you elaborate more in using for loops with if statements to delete values in rows that contain X,Y,Z values from another cell? Trying to automate deleting names from a list based after typing them in a cell. Any help would be greatly appreciated!
What's wrong, it won't delete the rows in column A with the words Date in them. Nothing happens actually.
Sub DeleteRowswithTextDate()
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 1).Value = "Date" Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub
❤️🔥👍