1. Go to Home tab -> Find & Select -> Go to Special
  2. Select Blanks. Click OK.

  1. The blank cells will be highlighted. Go to Home tab -> Delete. Select Delete Sheet Rows. The blank rows will be deleted.
Answer from Reddy Lutonadio on Stack Exchange
🌐
The Bricks
thebricks.com › home › resources › how to delete extra rows in excel that go on forever
How to Delete Extra Rows in Excel That Go on Forever
April 22, 2025 - Right-click the row or column header and choose "Delete" to fully remove it. Perform Regular Check-ups: Get in the habit of using Ctrl + End to periodically check the health of your most-used spreadsheets.
🌐
Reddit
reddit.com › r/excel › how do i delete endless blank rows at bottom of spreadsheet?
r/excel on Reddit: How do I delete endless blank rows at bottom of spreadsheet?
November 22, 2021 -

Working on a spreadsheet with a lot of data. However past the data there are endless blank rows. I want them permanently gone to reduce file size so only my data is in the file. How do I do it?

I tried highlighting all the rows, then right clicking and selecting delete rows. It didn’t work. For reference I’m on excel 2016.

Thanks.

Top answer
1 of 7
9
Delete the rows and then save the file, it'll fix
2 of 7
4
Use the button "Clean Excess Cell Formatting", from the "Inquire" tab.
How do I delete infinite blank rows? Oct 9, 2015
r/excel
10y ago
How to permanently delete blank rows in a csv? May 8, 2025
r/excel
last yr.
How to delete hundred thousands of unnecessary blank rows? Oct 29, 2019
r/excel
6y ago
Macro - Delete Blank Rows Jan 26, 2024
r/excel
2y ago
Office Scripts / delete blanks entire row Dec 10, 2021
r/excel
4y ago
More results from reddit.com
Discussions
How to Delete Thousands of Blank Rows at the bottom of a data set?
Hi Everyone, I have a data set of around 5723 rows of data. If I go to the bottom of my excel sheet, it goes down all the way to almost 11K rows. That's about 5K rows of nothing. This is causing my file to be slower and heavier. I know 1 solution to… More on learn.microsoft.com
🌐 learn.microsoft.com
10
262
May 16, 2022
How to end infinite rows on excel?
u/DragonFruit1591 - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns. More on reddit.com
🌐 r/excel
71
28
January 4, 2024
Delete Excess columns and rows
I have an excel file with multiple sheets in the workbook. Everyone one of the sheets goes up to column XFD and row 1048576. I have tried to the clear all... More on techcommunity.microsoft.com
🌐 techcommunity.microsoft.com
4
1
May 15, 2019
Endless rows at the bottom and unused columns to the right in my Excel spreadsheet ?
How do I delete or hide the endless rows at the bottom of my Excel spreadsheet and the unused columns to the right? I like to work with only the active columns and rows in my spreadsheets. I do not want to see the rows and columns that go on infinitely. … More on learn.microsoft.com
🌐 learn.microsoft.com
3
515
January 24, 2020
🌐
The Bricks
thebricks.com › home › resources › how to delete extra rows in excel that go on forever using ai
How to Delete Extra Rows in Excel That Go on Forever Using AI
February 20, 2025 - Sub ResetUsedRange() 'Declare variables for the last used row and column Dim lastRow As Long Dim lastCol As Long 'Turn off screen updating for better performance Application.ScreenUpdating = False 'Select the active sheet With ActiveSheet 'Find the last row and column that actually contain data On Error Resume Next 'To handle cases where no data exists lastRow = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row lastCol = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, Look
🌐
Quora
quora.com › How-do-you-delete-rows-in-Excel-that-go-on-forever
How to delete rows in Excel that go on forever - Quora
Answer (1 of 16): There are two possible aspects to this question that seem relevant. It could be that your worksheet and/or workbook is refreshing very slowly and you are aware that there may be many cells that you no longer use that contain data, formulas, links … content.
🌐
YouTube
youtube.com › spreadcheaters
How to delete rows in excel that go on forever - YouTube
02:04
View detailed instructions here: https://spreadcheaters.com/how-to-delete-rows-in-excel-that-go-on-forever/
Published: October 22, 2022
Views: 8K
🌐
Super User
superuser.com › questions › 1646255 › excel-how-to-delete-the-infinite-blank-rows
Excel - How to delete the infinite blank rows? - Super User
Top answer
1 of 3
1
  1. Go to Home tab -> Find & Select -> Go to Special
  2. Select Blanks. Click OK.

  1. The blank cells will be highlighted. Go to Home tab -> Delete. Select Delete Sheet Rows. The blank rows will be deleted.
2 of 3
1

Wrong answer... this will select all rows containing one empty cell, not empty trailing rows created by inserting more data deleted after by user.

Easiest way to get rid of them is to select all data from current sheet and paste it into new one (there will be no trailing rows)

🌐
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 5160991 › how-to-delete-thousands-of-blank-rows-at-the-botto
How to Delete Thousands of Blank Rows at the bottom of a data set? - Microsoft Q&A
Top answer
1 of 5
242

As suggested, go to the first blank row, press CTRL-SHIFT-DOWN ARROW, right-click and choose DELETE.
Save the file, size should go down.

If it is still large then go to the first blank column, press CTRL-SHIFT-RIGHT ARROW, right-click and choose DELETE. Save the file.

Sometimes I have to use SAVE AS and overwrite the file. Suggest you save with another name and see.

You will still see blank rows and columns but they are just displayed by Excel and do not take any space in the file.

2 of 5
58

I tried the solutions presented here in many variations and short of copy and paste into a new sheet, I can't get this problem resolved on my existing sheet... Isn't there some parameter that can be set for Excel that would limit the number of empty rows at the bottom. Hiding empty rows might get around the problem but let's face it, this is not a solution.

The fundamental issue is that scrolling is impractical with a sheet that has thousands of empty rows displayed.. This seems like a real bug in Excel and it's been there for years over the many versions of Excel that I've dealt with. Yes, I've seen that the fixes presented here can fix this problem but not in my current situation.

🌐
YouTube
youtube.com › spreadcheaters
How to delete infinite rows in Excel - YouTube
02:15
View detailed instructions here: https://spreadcheaters.com/how-to-delete-infinite-rows-in-excel/
Published: January 6, 2023
Views: 66K
Find elsewhere
🌐
ExcelDemy
exceldemy.com › home › excel formulas › how to delete rows in excel that go on forever?
How to Delete Rows in Excel That Go on Forever? - ExcelDemy
July 31, 2024 - In this method, we will use the Find & Select tool to delete blank rows. This method is useful when you’re working with large spreadsheets or when you need to change certain text repeatedly.
🌐
Reddit
reddit.com › r/excel › how to end infinite rows on excel?
r/excel on Reddit: How to end infinite rows on excel?
January 4, 2024 -

Hi everyone!

I'm working on a file which if I scroll all the way down, it never stops scrolling... there are thousands and thousands of blank rows and I'm struggling to delete them. Any thoughts on this one?

Thanks so much

Top answer
1 of 25
38
Are you saying that you sheet just has a lot of blank rows (modern XL comes with 1,048,576) or is your vertical scroll bar very tiny and making it hard to navigate?
2 of 25
33
I've had this problem before. Something is corrupt in the tab and it always thinks it's at a max number of rows. You have to open up a new tab, and cut and paste JUST your data table to the new tab. Delete the old tab when done and rename the new tab...
🌐
ExtendOffice
extendoffice.com › home › office tips › excel tips
How to delete infinite blank rows in Excel?
November 27, 2024 - When dealing with endless blank rows in your Excel worksheet, the Go To Special feature allows you to quickly select all blank rows then you can manually delete them.
🌐
Automate Excel
automateexcel.com › home › delete infinite rows / columns in excel & google sheets
Delete Infinite Rows / Columns in Excel & Google Sheets - Automate Excel
December 5, 2022 - This tutorial demonstrates how to delete infinite rows and columns in Excel and Google Sheets. If your Excel data has multiple blank rows
🌐
The Bricks
thebricks.com › home › resources › how to delete columns in excel that go on forever
How to Delete Columns in Excel That Go on Forever - Bricks
February 6, 2025 - Accidental Keystrokes: Typing a single space or character in a cell way out in column ZZZ and then deleting it can be enough. Excel remembers something was there. Stray Formatting: Applying formatting (like a fill color or border) to an entire ...
🌐
Microsoft Community
techcommunity.microsoft.com › microsoft community hub › communities › products › microsoft 365 › excel
Delete Excess columns and rows | Microsoft Community Hub
May 15, 2019 - I have an excel file with multiple sheets in the workbook. Everyone one of the sheets goes up to column XFD and row 1048576. I have tried to the clear all...
🌐
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 5011149 › endless-rows-at-the-bottom-and-unused-columns-to-t
Endless rows at the bottom and unused columns to the right in my Excel spreadsheet ? - Microsoft Q&A
Top answer
1 of 3
308

Hello Sarah5782

I am V. Arya, Independent Advisor, to work with you on this issue. You can go to last used column. Select next column which is unused, CTRL+SHIFT+Right arrow, right click and hide

Go to last used row, Select next row, CTRL+SHIFT+Down arrow, right click and hide

(To find last used row and cell, you can press CTRL+END)

2 of 3
13

Thank you for your response. It took several tries, but it worked. I appreciate your most kind assistance.

🌐
Reddit
reddit.com › r/excel › how do i delete cells which seem to go on forever?
r/excel on Reddit: How do I delete cells which seem to go on forever?
August 26, 2015 -

Hi. I had to concatenate some fields together, so I used a command like =A1&"-"&B1&"."&C1 and pressed CTRL+ENTER.

Now, I have a column that has the data I need for about 33000 rows, and everything below that is "-.". It is making my spreadsheet too big and Excel crashes every time I open it.

How can I solve this problem?

Thank you

Top answer
1 of 3
5

Go to the first row that you want to delete.

Click with the mouse in the number of the row, to select the whole row.

Press the following keys: Shift + Ctrl + Down arrow. Pressing and holding all of them.

You'll select all the rows bellow the first one you selected. If is necesary press Down arrow several times (while still holding Shift + Ctrl)

Once you reached the bottom of the sheet, mouse right click and Choose: Delete Rows.

Warning: Save it with a different name, just in case something went wrong, to don't loose your previous work. You will not see the results until you save the file. Save it, and then all the extra rows at the bottom will be dissapear.

Close and open the file again, after save it, to confirm.

Explanation: Shift key is to select, Ctrl key is to move to the next gap and the down arrow key indicates the direcction.

2 of 3
1

Turn off calculations (Google this) and then load the file.

Or alow for blank handling e.g. 

IF(ISBLANK(A1),",CONCATENATE(A1,"-",B1......))
🌐
Quora
quora.com › How-do-you-get-rid-of-thousands-of-extra-rows-in-Excel
How to get rid of thousands of extra rows in Excel - Quora
Answer (1 of 2): Interesting question. There are only a couple reasons I can think of for wanting extra ‘blank’ rows deleted. First, the extra ‘blank rows’ were once ‘really used’ so the scroll bar is tiny when it seems like it should be large. Second, if you have not set up a print ...
🌐
Excel Forum
excelforum.com › excel-general › 604214-delete-all-the-extra-blank-rows-at-the-bottom-of-spreadsheet.html
Delete all the extra blank rows at the bottom of spreadsheet
I just put this to work on fancy Excel phone listing for our company. The size of the spreadsheet went from 888KB to 64 KB, for exactly the same reason our original poster shared ... Note that this works going across columns as well when you're on the last column of applicable cells, you can use your technique with the Right arrow to highlight and delete anything in that direction as well.
🌐
The Bricks
thebricks.com › home › resources › how to remove excess rows in excel
How to Remove Excess Rows in Excel
April 22, 2025 - Dim lastUsedRow As Long Dim lastUsedCol As Long 'Disable screen updating to speed up the macro Application.ScreenUpdating = False If WorksheetFunction.CountA(ActiveSheet.Cells) > 0 Then 'Get the index of last row with data, including formatted cells lastUsedRow = ActiveSheet.UsedRange.Rows.Count 'Delete any columns larger than last cell used lastUsedCol = ActiveSheet.UsedRange.Columns.Count 'Delete rows below the last used row to bottom of sheet On Error Resume Next 'Handles the case where there's nothing to delete ActiveSheet.Rows(lastUsedRow + 1 & ":" & ActiveSheet.Rows.Count).Delete On Erro
🌐
Microsoft Community
techcommunity.microsoft.com › microsoft community hub › communities › products › microsoft 365 › excel
My excel sheet keeps recreating rows after I delete them | Microsoft Community Hub
Top answer
1 of 5
2
Select the row immediately below your data and press Ctrl+Shift+End to select all blank rows below your data. Press Ctrl+- and select Rows, then click Ok to delete all the selected rows. Save your file. If you now press Ctrl+End, you will jump to the rightmost cell in the last row of your data, which indicates that you successfully deleted all blank rows below it.
2 of 5
0
monica91 100 rows of … , what exactly?The sheet will always have 1048576 rows, so any rows you delete will be replaced by blank rows.These will reflect any whole column formatting that might be in play.