Fastest Way to Delete Empty Rows in Excel (New)
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- This video shows you the fastest and safest way to delete empty rows in Excel. There are lots of different ways to remove blank rows in Excel, like using go to special, or using filters, but one of them is better than the rest!
WHAT TO WATCH NEXT:
Best 11 Excel Tips You NEED to Know! - • Best 11 Excel Tips You...
5 Things You're Doing WRONG in Excel - • 5 Things You're Doing ...
How to Bring AI INSIDE Excel (New Function) - • Video
NEW Checkboxes are WAY BETTER in Excel (in 5 Minutes) - • Video
How to Automate Excel with Custom GPT Actions (In 5 Minutes) - • How to Automate Excel ...
Excel Course: www.easysheetslearning.com/p/...
FREE Excel Office Script (delete rows): easy-sheets.ck.page/6e0c8d3aa6
FREE Excel shortcuts PDF: linktr.ee/easysheets
Prosper Spark Excel Consulting: www.prosperspark.com/easy-she...
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!
Please share the link for script for these topic mostly use in industry
Where is the link to the script?
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
In very large data tables: sort first
That will work, but my script is faster!
@@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!
@@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.
@@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.
Amazing video brother.
You’re a teacher by nature. 🙌🏽
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.
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.
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.
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.
Thanks for the insight! Super helpful (as usual)
Thanks for making this type video. it really helpful
this video saved me forty minutes thank you
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
Add column with counta, filter 0s, delete rows, done.
Works but very slow compared to the script
@@easy_sheets but you don't need a script, so unless someone deals with Such cases on a daily basis, is better
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
Wow loved it
Thanks a ton
Just found your channel. So useful 😊
Thank you 🙏🏽
Someone give this man a raise
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.
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.
This is true, a lot of company banned the automate command.
Yea, I couldn't find it too. Thanks for an helpful tip tho
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"
Works but way slower than the script!
@@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. 🙂
@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?
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.
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.
great video thanks, where did you learn how to write scripts ?
Have a web/mobile development background!
Güzel
I think, make sub column then use function counta, then filter values= 0 on that column. After that, delete rows of result filter
Realt great 👍
How can I get the script??
link in description
How about sorting all columns?
I use count formula
the fastest way by far is to use asap utilities. takes 3 seconds max.
49 bucks, per year, lol
bro use the free edition.@@Acheiropoietos
But I have no data script due to lower version of MS office
Kindly let me know which version has this tool
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.
The cool solution, but not for beginners. Sorting can also be used.
Why isn't this a buildin function outside power query?
So does subscribing to get this script in email cost money?
Can the script be edited to hide blank rows instead of deleting them?
Yes you can do that!
@@easy_sheets Thank you! This will help me refine an inventory tracking tool.
I did that first method of just manually deleting blank rows and I was done before he even got to the last explanation.
Hi
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
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
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!
Fastest? No way 😂
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
There's no Automate in my Excel.
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...
Unfortunately excel doesn't have mind reading capabilities yet!
I usually do this job by Go to Special (mistake no. 2). Thought I was doing it the right way 😅😅😅
huh, wasn't this uploaded a long time ago
This is a new vid and script. I did upload a short about this topic a while ago using vba