Made a slight change to the script so it's even faster. Now you just select all the columns in your table or range, then run the script. Done!
@raviparmar14146 ай бұрын
Please share the link for script for these topic mostly use in industry
@noelpooley81715 ай бұрын
Where is the link to the script?
@FilmscoreMetaler6 ай бұрын
1. =textjoin(,,a2:f2) into g2, drag down 2. ctrl+shift+L in g1, select empty 3. delete empty rows 4. ctrl+shift+L in g1, delete helper column
@FilmscoreMetaler6 ай бұрын
In very large data tables: sort first
@easy_sheets6 ай бұрын
That will work, but my script is faster!
@rainthunderrelex6 ай бұрын
@@easy_sheets In this video, Yes.. but in real world it su*ks. Try selecting data with more than a thousand blank rows and see how fast it is!
@rainthunderrelex6 ай бұрын
@@easy_sheets Insert column in A > Type 1 > press Ctrl+Down > Press Right > Press Ctrl+Up > Press Left > Type 1 > Copy 1 and Press Ctrl+Shift+Up > Paste > "Now there is no gap in table" > Apply filter > Filter B with blanks > Select All 1s in A > Press Alt+; > Press Ctrl+- > Hit Enter. All rows are gone no matter how many. It looks long like this but it takes less then 10 seconds.
@painpeko88056 ай бұрын
@@rainthunderrelex I tried this method but I think I'm doing it wrong? When I select All 1s in A and delete, it deletes the rows that are in Column B with blanks. But I have some data in Column C where B had a blank - that data is deleted.
@AR-rs2dy6 ай бұрын
I wld use the COUNTA function in the column after where the data ends and then filter on rows that contain the result "0" and delete. Even if u have a lot of columns it won't take long. I like the power query option as well.
@psychologicalprojectionist5 ай бұрын
Good solution. I would use a custom sort with all the rows, which should end up with all the all blank rows on the top or bottom. If you add an index column you sort back to the original order after. But obviously the script is going to be quicker and more versatile. You add a text join column, but I guess that is slightly worse than counta.
@JJ-ZUMA6 ай бұрын
Amazing video brother. You’re a teacher by nature. 🙌🏽
@Hyposonic6 ай бұрын
Let's use a Macro, VBA, JS, Python, DAX, Scripts, Power Query, add-ins, extensions, and more. What a nightmare! A coherent vision from MS leadership decades ago would've resulted in an easier, smaller, more stable application. Instead, we have a Franken-App.
@oso12485 ай бұрын
Excel is one of the most powerful and capable apps that exists. It can handle anything from someone that types things into a cell to people that create fully automated apps that poll, calculate and display anything that’s accessible on the internet. If you’re looking for a neutered variant of excel, libre office has an app named ‘calc’; it’s free, can handle most excel file types and is smaller and easier to use. I use excel specifically because I can apply almost any programming language to it and it comes pre packaged with a rather powerful language and code editor to boot. That Frankenstein app is so popular because anyone can pick and choose whatever pieces of Frankenstein they need to use and never touch the rest of them. In order to make something easier enough for everyone to use you have to severely limit its capabilities; excel has arrived at a pretty good balance, it takes some learning to start using it for newbies but still offers pretty robust capability to advanced users. Beyond excel’s capabilities you’re really just going custom purpose built software.
@adibasyed20885 ай бұрын
this video saved me forty minutes thank you
@winter97986 ай бұрын
Very good - i use this but - most people in my office do not have access to scripts - so i did a 3rd solution. Not great but easy...add helper column, countif cells are blank - so if you have 10 columns and amount =10, simple filter and all gone. not perfect - but for many many companies without tech support of office 365 - best solution for them
@zensx5548Ай бұрын
i usually create a column with a countblank formula. Something like: if countblank(cell1 to cell99)=99,"blank row","not blank row". then i filter for "blank row" text and nuke the entire selection. depending on your work environment, script stuff/automation could be locked down but the powerBI thing looks great, i'll have to try that.
@raviparmar14146 ай бұрын
Thanks for making this type video. it really helpful
@arturcostasteiner97356 ай бұрын
Very useful, thank you! Could you please recommend a method to sort the rows on a spreadsheet? If the formulas in a row depend only on cells in the same row then no problem. But if the cells in the range to be sorted depend on cells outside of the range, then you have to turn the cells into their values. That's way impractical
@ohdjrp46 ай бұрын
Not all Excel users know VBA or this new Script feature. The select "BLANKS" option inside the "PASTE SPECIAL" feature is always easy provided you select the anchor column (in your data, it is in the column A - "Store"), then press Control key + minus sign. That will delete those extra rows without problem.
@Chamaeleo6 ай бұрын
Add column with counta, filter 0s, delete rows, done.
@easy_sheets6 ай бұрын
Works but very slow compared to the script
@Chamaeleo6 ай бұрын
@@easy_sheets but you don't need a script, so unless someone deals with Such cases on a daily basis, is better
@mkfadzli6 ай бұрын
The solution is helpful. Unfortunately, not all Excel versions have automated features. Even if they do, office admins may not allow it, depending on the company.
@kontraksionista6 ай бұрын
This is true, a lot of company banned the automate command.
@maryozonkwor-ei8vw2 ай бұрын
Yea, I couldn't find it too. Thanks for an helpful tip tho
@Sinogard6 ай бұрын
1. Format as table 2. At the end add column with header "BLANKS" and with function "=COUNTBLANK()" where into function parameter add range of all previous columns on the row 3. Sort column "BLANKS" from largest to smallest 4. If you have the 6 columns originally, the full empty will be on the top 5. Delete all rows with value 6 in column "BLANKS" 6. Delete column "BLANKS"
@easy_sheets6 ай бұрын
Works but way slower than the script!
@Sinogard6 ай бұрын
@@easy_sheets True. But beauty of the solution is, that anybody can do it. More importantly even if they do not have your script with them. But if they do or can easily look up this episode, then it's faster. 🙂
@BigNope36 ай бұрын
Someone give this man a raise
@goosh316 ай бұрын
Thanks for the insight! Super helpful (as usual)
@normundserglis17956 ай бұрын
The cool solution, but not for beginners. Sorting can also be used.
@rawrss6 ай бұрын
@Easy Sheets any advice on a old Excel workbook that appears to have data only up to say row 5,000 but if you press ctrl+end you get to somewhere in the millions? No amount of deleting rows/clearing contents work. Do you have any advice on how to tackle?
@easy_sheets6 ай бұрын
Usually when that happens it's because of the cache, should clear when you restart excel but if that doesn't work im not sure.
@MrOptima6 ай бұрын
You can reduce it back to proper size by doing the following: - select all blank columns after your last actual data column and then delete them - select all blank rows after your last data row and do the same - save your workbook and re-open It should now show the proper size. This issue can happen if you edited any cell way down and/or to the right in the past. Or, a cell might have a space character in it and count as part of your full range. By deleting all columns and rows at the edges, Excel replaces them with blank ones which are no longer part of its cache. I fixed a lot of worksheets with the same issue in the past decade and it is usually the simplest way to deal with it.
@AK-mg5rh6 ай бұрын
great video thanks, where did you learn how to write scripts ?
@easy_sheets6 ай бұрын
Have a web/mobile development background!
@nsngoisaovietinstar57796 ай бұрын
I think, make sub column then use function counta, then filter values= 0 on that column. After that, delete rows of result filter
@lifeofexplorerwithmra31086 ай бұрын
Wow loved it
@lovewenwin6 ай бұрын
Thank you 🙏🏽
@Trang-A-Lang6 ай бұрын
Just found your channel. So useful 😊
@thoreberlin5 ай бұрын
Why isn't this a buildin function outside power query?
@PatG-ym2qh6 ай бұрын
How about sorting all columns?
@Sawabeq6 ай бұрын
Thanks a ton
@thatdumbguy60754 ай бұрын
So does subscribing to get this script in email cost money?
@kuldeep.30126 ай бұрын
But I have no data script due to lower version of MS office Kindly let me know which version has this tool
@MrEddyTse6 ай бұрын
From Microsoft Support, it says only Office 365 Enterprise or Education version will be able to turn on the Automate Tab, other more popular versions, such as O365 Family version, will not have it. They suggest to use User Voice to suggest Microsoft Developer Teams to change their policy.
@marwanattya80676 ай бұрын
Realt great 👍 How can I get the script??
@easy_sheets6 ай бұрын
link in description
@StevenLee-xj6gx6 ай бұрын
I use count formula
@elymX6 ай бұрын
the fastest way by far is to use asap utilities. takes 3 seconds max.
@Acheiropoietos6 ай бұрын
49 bucks, per year, lol
@elymX6 ай бұрын
bro use the free edition.@@Acheiropoietos
@sonerguney32256 ай бұрын
Güzel
@hethemystery6 ай бұрын
i had a file with 4500 rows alternate rows were empty had to clean it tried many methods in the end i gave it to chategpt and asked to remove teh empty rows and in lest than 10 seconds it was done. chatgpt is not the solution everytime
@Mark-qg8hm6 ай бұрын
I did that first method of just manually deleting blank rows and I was done before he even got to the last explanation.
@dangkhanhlinh4766 ай бұрын
Fastest? No way 😂
@klve16 ай бұрын
Can the script be edited to hide blank rows instead of deleting them?
@easy_sheets6 ай бұрын
Yes you can do that!
@klve16 ай бұрын
@@easy_sheets Thank you! This will help me refine an inventory tracking tool.
@stopdrinkingleftskoolaid65106 ай бұрын
Why would make the Go To : select blanks ignore empty cells? There is nothing hidden in these cells. No spaces. No hidden text. No hidden formulas etc. thanks
@ihuitson6 ай бұрын
Why not use a helper column, where you Counta() the number of entries in the row, then filter by this and delete the Rows with 0 entries
@easy_sheets6 ай бұрын
You can do that! The cool thing about using the script is that it stays available in new workbooks once you set it up, so it's super fast!
@MowaisoOwais6 ай бұрын
Hi
@MrPensive5 ай бұрын
There's no Automate in my Excel.
@yrot11235 ай бұрын
why do you make thing more complicated, suppose to make thing easier for the users, not having another script written by someone, then remember all of these commands and short cut keys... too many damn short cut keys to remember.. I don't need another stupid script or add on key...
@easy_sheets5 ай бұрын
Unfortunately excel doesn't have mind reading capabilities yet!
@lydethful6 ай бұрын
I usually do this job by Go to Special (mistake no. 2). Thought I was doing it the right way 😅😅😅
@Dante03976 ай бұрын
huh, wasn't this uploaded a long time ago
@easy_sheets6 ай бұрын
This is a new vid and script. I did upload a short about this topic a while ago using vba