-1

I do not want my question to be mistaken for deleting a row or deleting blanks.

In a number of cases I have come across excel files that have an excessive amount of rows or columns. I would like to know how to remove the extra rows entirely.

For example the excel file currently in question has data from row 1 to row 2000 but there are 66,000 rows in the excel sheet. I would like to remove all the extra rows so that when I scroll from row 1 to row 2000 that my scroll bar at row 2000 is at the bottom of the scroll bar.

Please Note: Copy and pasting the data to a new sheet is not an option. Currently with all the extra rows my scroll bar only moves maybe 5% of the way down from the top of the scroll bar.

Solution to answer: Delete all the rows scroll to the top of the sheet, save, close , reopen...

Exclusiveicon
  • 33
  • 9
  • You can hide all the rows you do not want. But it sounds like you have some errant data in lower rows that is causing the scroll issue. Highlight all the rows below your data and hit delete. Then close and open Excel. the scroll bar should now be correct. – Scott Craner Mar 01 '18 at 18:17
  • The excel File is 3.4mb and slow because of the extra rows.. So hiding the rows wouldn't change this. I selected all the rows like you said and hit delete, saved and reopened and nothing changed, all the rows are still there. I also selected all the rows and right clicked and hit delete, saved, closed and reopened and nothing changed. all the rows are still there. – Exclusiveicon Mar 01 '18 at 18:27
  • The rows will always be there, Excel will always put them in, You can hide them but not get rid of them. But your scroll bar should bottom out at the last used row. – Scott Craner Mar 01 '18 at 18:29
  • Ok, what I was doing wrong was saving while looking at row 66,000 so when I reopened I was still looking at 66,000. I scroll up to row 1 and saved and reopened and all the extra rows are gone now!!! Thanks!! Note: this was after deleting all the rows and saving and reopening like I mentioned above. – Exclusiveicon Mar 01 '18 at 18:40

3 Answers3

0

As you know, if you highlight cells or rows or columns in Excel and hit the Delete button, the contents are removed but the cells remain as they are (albeit empty). The rows do not scroll up, nor the columns scroll left.

However, if you highlight your rows (not just the cells, but the entire rows using the grey border area on the left) and use the Right mouse button menu - to select Delete - then the cells are removed from the Worksheet. (NOT the delete button on your keyboard)

After this operation, rows will scroll up, or columns will scroll left to fill in the deleted range. This should also reduce the (apparent) size of your worksheet.

But - as you scroll down, Excel will add more rows automatically. There are other ways to Hide these rows, but the trick above should remove the rows that are troubling you.

Another option from here:

Limit Rows and Columns without VBA

An alternative, and more permanent method for restricting the work area of a worksheet is to hide the unused rows and columns.

To hide the rows and columns outside the range A1 : Z30, the steps would be:

  • Click on the row heading for row 31 to select the entire row.
  • Press and hold down the Shift and Ctrl keys on the keyboard.
  • Press and release the Down Arrow key on the keyboard to select all rows from row 31 to the bottom of the worksheet.
  • Right-click in the row headings to open the context menu.
  • Choose Hide in the menu to hide the selected columns.
  • Click on the column heading for column AA and repeat the above steps two to five above to hide all columns after column Z.
  • Save the workbook and the columns and rows outside the range A1 to Z30 will remain hidden.
Grantly
  • 2,546
  • 2
  • 21
  • 31
  • I have tried your suggestion and it does not reduce the number of rows. your right that when you delete the rows excel shift the rows up or how ever you choose when prompted but it does not reduce the number of rows. – Exclusiveicon Mar 01 '18 at 18:31
  • @Exclusiveicon Thanks for trying this, I have tested it also and it seems to work on my version of Excel. If you use CONTROL END does it take you to row 2000, or row 65000 approx? DId you highlight and remove all the rows from 2000 - to the final row? – Grantly Mar 01 '18 at 18:34
  • @Exclusiveicon Added an alternative solution to my answer – Grantly Mar 01 '18 at 18:43
0

Try the following short macro:

Sub rowHIDER()
  Dim N As Long

  N = Application.InputBox(Prompt:="Enter the number of the last row you want visible: ", Type:=1)
  Range(Cells(N + 1, 1), Cells(Rows.Count, 1)).EntireRow.Delete
  Range(Cells(N + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
End Sub
Grantly
  • 2,546
  • 2
  • 21
  • 31
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I wish I could, the excel file was downloaded years ago from a 3rd party that has a VBA program in it that we use, the file is not available for download anymore and the VBA code is locked/password protected. – Exclusiveicon Mar 01 '18 at 18:36
0

I often get these workbooks with ridiculous amounts of empty rows - Delete the rows and then save it again. Close. Re-open. Hopefully they're gone for good.

jamheadart
  • 5,047
  • 4
  • 32
  • 63