Thanks for describing the possibility of deleting rows with VBA so precisely
@guideglobal2140
2 жыл бұрын
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.
@stevesandike252
4 жыл бұрын
subscript out of range before it starts, on the set ws line
@nitebone
4 жыл бұрын
I change the "text" to the sheet number and it worked for me.
@michaelmurphy5999
4 жыл бұрын
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?
@jonsedman3416
2 жыл бұрын
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
@darrenbonnar1504
Жыл бұрын
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
@mjchrist828
5 жыл бұрын
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?
@forexbeat
5 жыл бұрын
Please enter the correct sheet name. Regular range is the sheet name of this youtuber might not be yours
@canefan17
5 жыл бұрын
Can it delete rows that meet IF logic? E.g if column C date is less than column G date, then delete.
@lenorawillis-photonobics4182
4 жыл бұрын
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.
@1982degoel
Жыл бұрын
is it possible to delete one cell (not entire row )based on cell values ?
@Monnet6777
2 жыл бұрын
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?
@Trupticool
3 жыл бұрын
Thank you very much, very useful video
@carlosgaspar4207
3 жыл бұрын
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
@Railien24
4 жыл бұрын
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?
@tannertucker22
Жыл бұрын
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?
@tonywillingham8109
2 жыл бұрын
How do you prevent this method from always deleting the first row, which the Auto Filter will never hide?
@tamilsnowgreen
4 жыл бұрын
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?
@kimbowman4063
4 жыл бұрын
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?
@anthonylobello5365
4 жыл бұрын
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.
@areliarana1351
10 ай бұрын
🎉 Genius
@monicathatcher1917
4 жыл бұрын
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.
@9019828585
2 жыл бұрын
Great video ! Working absolutely fine , thanks a ton !
@abbuthefame
3 жыл бұрын
If we want to find some content and delete .. if we don't know exact content
@vijaysahal4556
4 жыл бұрын
very useful sir 👍👍👍👍👍👍
@angon25
4 жыл бұрын
New favorite channel!!!
@shawdowsanddust5301
3 жыл бұрын
Outstanding code. Thank you sir.
@HowtoHacks
3 жыл бұрын
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.
@xblades1984
4 ай бұрын
And what if data exceeds row G1000 bro? Please always show something which gives permanent solutions and not temporary ones
@james3603
3 жыл бұрын
what if i have multiple criteria to delete , like if the status is closed, resolved, active, etc.?
@dinyarbuhariwalla1120
3 жыл бұрын
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?
@nitebone
4 жыл бұрын
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."
@creatorshappylife1256
2 жыл бұрын
Thank you so much
@Sarfoo93
4 жыл бұрын
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?
@scottgaines2677
3 жыл бұрын
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!
@scottgaines2677
3 жыл бұрын
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!
@alexandresirois8338
4 жыл бұрын
Works great, but my excel keeps crashing at the end of the code before clearing the filter. Any idea why that might happen?
@TheMuraleee
3 жыл бұрын
Hello Brother, am getting debug on "Regular Range" Line no 3.. Can you plz help me to fix it
@srinivasannarayanaswami5378
2 жыл бұрын
Simply Superb. Very useful for me. Appreciate it :-)
@yisrael1663
4 жыл бұрын
How do I apply AutoFilter for all rows with dates prior to today's date in VBA, then delete the rows?
@tannertucker22
5 жыл бұрын
Another awesome, well-explained video. Thank you Jon.
@ExcelCampus
5 жыл бұрын
Thanks so much Tanner! :-)
@tannertucker22
5 жыл бұрын
@@ExcelCampus Well-deserved. Have you thought about adding how to preserve the deleted rows by pasting them to a new worksheet?
@sekarr261
4 жыл бұрын
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,
@stephaniealdana8624
4 жыл бұрын
This was a life safer! Thank you!!
@sonyarodriguez9572
5 жыл бұрын
Hi Jon, I would like the field to apply to multiple columns, do I simple add Field:4,5,6 for example?
@qianpiscessay4728
4 жыл бұрын
Thanks for such good video.
@abbisandi
5 жыл бұрын
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?
@greggowaffles
5 жыл бұрын
hi abbisandi. were you ever able to solve this? i would love to help!
@yerzhankosbayev6436
3 жыл бұрын
@@greggowaffles Criteria1:="=*text*"
@imapkru
3 жыл бұрын
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.
@TheFall0fSummer
3 жыл бұрын
a bit late and not the video poster, but yes you can use Criteria1:="Spanish"!
@Dina_Darling
5 жыл бұрын
Awesome. Thanks!
@ExcelCampus
5 жыл бұрын
Thank you Dina! :-)
@redfeather22sa
4 жыл бұрын
Your Very Good !! :) Thank You from SO !! :)
@empty_mirror
4 жыл бұрын
Thank you! :)
@mnesevati
4 жыл бұрын
There is a problem, for example: if you re-run the code again (value bases on table), an error will pop up!!!
@md.saifulislamtuku9303
5 жыл бұрын
Thanks
@rajeshmajumdar4999
5 жыл бұрын
Thank you👍
@wayneedmondson1065
5 жыл бұрын
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
@ExcelCampus
5 жыл бұрын
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! :-)
@wayneedmondson1065
5 жыл бұрын
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
@abbisandi
5 жыл бұрын
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?
@LoeblComServices
5 жыл бұрын
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.
@alexhoward864
5 жыл бұрын
bump, as I also need to know this. 3 months with no reply/answer?
@LoeblComServices
5 жыл бұрын
@@alexhoward864 What does your worksheet look like?
@adriansolvi7096
5 жыл бұрын
Great video!!!
@ca.lakshminarayanreddyjamb9087
5 жыл бұрын
Awesome Jon.
@harendrakumarsingh7699
10 ай бұрын
Can we use it in Google sheets
@ExcelCampus
10 ай бұрын
These VBA macros are based on Microsoft Excel. Google Sheets has its own coding language called App Scripts where you could do something similar.
@ExcelCampus
10 ай бұрын
These VBA macros are based on Microsoft Excel. Google Sheets has its own coding language called App Scripts where you could do something similar.
@HoangVu-yf1ix
5 жыл бұрын
I think we should clear filter before using auto filter for better result.
@ExcelCampus
5 жыл бұрын
Great point Hoang! I'll update the post and macro. Thanks! :-)
@franktroplis
3 жыл бұрын
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.
@DerekElliott-qn2kz
Жыл бұрын
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?
@MohAboAbdo
5 жыл бұрын
Thanks ... Thanks
@keithbender1476
4 жыл бұрын
Attempting to delete rows with the word "Total" in a cell.
@giuliagrigoli5886
5 жыл бұрын
Great video! thanks a lot or that!
@vivaladavid550
4 жыл бұрын
subscript out of range
@Msantora908
4 жыл бұрын
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!
Пікірлер: 81