You're my teacher n. 1 for VBA. Please don't stop posting. even if excel has improved a lot, with new formulas, power query and power pivot, we still need our good old VBA !!!
It's interesting to a see a professionals take on this. The first problem, I would have just filtered the column searching for 0, then deleted all rows, then removed the filter. On datasets of a few hundred rows, this works very fast.
I am using this method: Sort, Filter, Delete visible lines exept header, filter off: ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
Mr. Paul, I find your videos very helpful in my tasks. Can I ask a question? My dataset column 'D' has integer values in it, along with zeros in some cells. How can I force the Find function to just look for '0' in whole cell, instead of looking for '0' in figures say 76,000?
Lovely, Paul, and pretty ironic as I put together a very similar routine yesterday, except I captured an array of the values across all columns within the records with the same 'famCode'. Then I looped across the rows in each column of that array, storing the first value as the string for comparison and comparing each subsequent value within that column of the array against it. If it didn't equate to the comparison string, I mapped out that column in the array to the equivalent range in the worksheet, applied formatting to highlight and exited out of that column to resume looping. Very much inspired by your Array-Dictionary combination techniques for speed. 🤩🙏
Hi Paul, I am from Senegal. I really appreciate your sharing knowledge. However, I would like to know how I can't subscribe through payment by card from my country to get more of your lessons.
@@Excelmacromastery ah yes, I fell into the trap of thinking what's easiest when doing it manually through the UI. Execution wise, I can see how your method would be faster.
Hey man.. So call me late to the party, but is vba seriously dead with subscr online ver of MS365? Even the paid version? Am lookin to finally upgrade my old excel version...Reading all about the benefits of buying the subscription MS 365 office vs the old one time buy/install version. No create or run any vba at all? How is that possible with millions of vba files out there. I hear office 365 excel desktop ver still does vba...But whats the point...u cant give it to someone using 365 online?!!
MS365 I'd subscription based with updates where as Office 2021 is once off payment. Both are desktop versions of office. 365 includes an online version of Excel. Online isn't replacing desktop. It's just an alternative way of editing an Excel file.
Take home message: MS Excel is a dinosaur. (You could have read the data into an array and done the deletion there, on the first one too. Why didn’t you?)
I had a sheet with 16,000 rows with a similar problem. I tried both methods: delete via array, and delete after sorting. Sorting is way faster, especially when the cell contents are formulas.
You're my teacher n. 1 for VBA. Please don't stop posting. even if excel has improved a lot, with new formulas, power query and power pivot, we still need our good old VBA !!!
Thanks. I plan to keep going.
Love to see you back again, keep bringing these
Thanks
It's interesting to a see a professionals take on this. The first problem, I would have just filtered the column searching for 0, then deleted all rows, then removed the filter. On datasets of a few hundred rows, this works very fast.
Brilliant video! I always learn something new when I watch one of your videos. If you are able to, could you share the Timer code?
It's been a while Mr Paul Kelly, since a last video about Vba. I still need learn a lot from U.. please don't get bored to teach us..
Thanks🙏
Nice video, but may I ask for a link to that Timer class? Thanks in advance!
If it takes you 4 minutes to loop over 10 rows of data, you don't need a better algorithm; you need a new computer.
Not to loop but to delete individual rows.
You're missing the point: the time is spent deleting even a single row in a list of "thousands of rows."
😊😊😊😊😊😊😊😊@😊😊
Great (as always) you shed new light on what we have done by habit
thank you very much for this video which is interesting. please can you share with us the excel file of this course. best regards
I find it interestinf you seem to get a performance increase by disabling events even if you don't have any actions assigned to those events.
I am using this method: Sort, Filter, Delete visible lines exept header, filter off: ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
Mr. Paul, I find your videos very helpful in my tasks.
Can I ask a question? My dataset column 'D' has integer values in it, along with zeros in some cells. How can I force the Find function to just look for '0' in whole cell, instead of looking for '0' in figures say 76,000?
Lovely, Paul, and pretty ironic as I put together a very similar routine yesterday, except I captured an array of the values across all columns within the records with the same 'famCode'. Then I looped across the rows in each column of that array, storing the first value as the string for comparison and comparing each subsequent value within that column of the array against it. If it didn't equate to the comparison string, I mapped out that column in the array to the equivalent range in the worksheet, applied formatting to highlight and exited out of that column to resume looping.
Very much inspired by your Array-Dictionary combination techniques for speed.
🤩🙏
That is a coincidence. Thanks for the feedback Ben.
In your 2nd topic, i think using the Dictionary method is the best option in an instance when "Count FC" inidcator isnt present.
Thank you, Paul!
I would like a tutorial on VBA interaction with messengers: Telegram, Viber, WhatsApp. Your best practices on this topic.
Your work is excellent. Thanks for sharing.
Glad you like it
I think this can also be done using Union and delete all the 0's at once. Sorting won't be required.
Thanks for wonderful technique that save a lot time.😊
You're welcome.
Hi Paul, I am from Senegal. I really appreciate your sharing knowledge. However, I would like to know how I can't subscribe through payment by card from my country to get more of your lessons.
Create table. Load in power query en load back in Excel.
I’m wondering if the external workbook references is what was making the Delete slow if the workbook isn’t open…
Excellent tutorial!
Thanks
Can you Upload Sample Data- Excel Sheet ...Which will help many people....
Paul, link please file from this tutorial to download.
1st example, why not simply Data/Filter/Autofilter for zeros and then delete the filtered rows?
It is slower than the method I used.
@@Excelmacromastery ah yes, I fell into the trap of thinking what's easiest when doing it manually through the UI. Execution wise, I can see how your method would be faster.
this is not the great solution without toucing the data you shld make the code run faster
Please give the files, Or Just the code in a texf files, it would help a lot..
How to scrape data from web with login
it looks like you dont like the for loop in your code.
Заметил русское название файла))
Hey man.. So call me late to the party, but is vba seriously dead with subscr online ver of MS365? Even the paid version? Am lookin to finally upgrade my old excel version...Reading all about the benefits of buying the subscription MS 365 office vs the old one time buy/install version. No create or run any vba at all? How is that possible with millions of vba files out there. I hear office 365 excel desktop ver still does vba...But whats the point...u cant give it to someone using 365 online?!!
MS365 I'd subscription based with updates where as Office 2021 is once off payment.
Both are desktop versions of office. 365 includes an online version of Excel.
Online isn't replacing desktop. It's just an alternative way of editing an Excel file.
Mn
Take home message: MS Excel is a dinosaur.
(You could have read the data into an array and done the deletion there, on the first one too. Why didn’t you?)
I had a sheet with 16,000 rows with a similar problem. I tried both methods: delete via array, and delete after sorting. Sorting is way faster, especially when the cell contents are formulas.
The solution required keeping the formatting of the records and because sorting was faster.
@@quench100 How about Power Query. It would be fine befire more. You opened the VBA editor.
@@TP-om8of I've never used Power Query, I'm using Excel 2013.
Yep. Power Query is an ETL programm. Perfect for cleaning data.
=MIN(FILTER($C$2:$C$1048576,$B$2:$B$1048576=b2,""))
but its always good to know a new VBA approach.