Hi, Excellent tutorial! One question I still have. I have a sheet with 110,000 rows, with 35381 blank rows. When I try your second method 'Remove blank rows using a key column' after I select the delete row option I get the following error: 'Excel cannot create or use the data range reference because it is too complex. Try one or more of the following: - use data that can be selected in one contiguous rectangle - use data from the same sheet' obviously I'm working on the same sheet, and this method is only works because we're not selecting a contiguous rectangle. I'm wondering what my options are. Thanks in advance for your help, Francois. Use the Layout tab to delete columns and rows in a table. Click in a table cell located in the row or column you want to delete. On the Layout tab (next to the Table Design tab), click Delete. Actually, I just found a great, super quick way to get rid of extra rows. 1) save the original Excel file as 'tab-delimited' text file, then. 2) open that file in notepad or TextEdit (Mac), then 3) in the text app select the whole empty row (triple click on Mac) 4) do a 'find' in the text app, paste the empty-row and click 'replace' with empty field (nothing) 5) then just click 'replace all' and boom! All those damn empty rows gone! Then just copy-paste back into Excel:) At any rate, I tried this on my Mac, and it worked like a charm, I'm not sure if Notepad on the PC has the same find-replace as the Mac's TextEdit, I assume it does, but if it doesnt then maybe you can try another basic word-processing app that allows simple find-and-replace Good luck! Again, only in case I'm not the only one. Here's what works for me: 1) Save a copy because the following process will delete the header row and you'll need it back. 2) Click in Cell A1. 3) Click Cmd+Shift+End to select to the end of used cells. 4) Go to Data and click on Filter 5) There's now a drop down arrow in every column. Click on the one at A1. 6) Everything you do in this dialog box kind of only happens to the items you can see, believe it or not. So go to the lower right of the dialog box and stretch it down as far as you can. 7) Uncheck 'Select All.' This may or may not actually deselect all. Scroll down to see if any other items are still selected. If so, try clicking on it. It selects that one and probably deselects many other items but not necessarily all of them. The taller the dialog box, the better your chances of getting most of them. Repeat this process until you truly have nothing selected. 8) Scroll down to the bottom and check '(Blanks)'. Close the dialog box by clicking outside it anywhere. 9) Go to Edit. What used to say 'Delete' now says 'Delete Row'. This is such a big deal to Microsoft that there'll be a warning box.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2019
Categories |