Share via

Change usable area in Excel worksheet

Anonymous
2016-06-23T18:12:42+00:00

Hello! Thanks for reviewing my issue.

I have a worksheet with student demographic info that links to another worksheet which compiles demographic info. At one time, I limited the first sheet to 175 rows and I can't for the life of me remember how I did it (definitely not using anything too complicated like VBA stuff). I do know that these rows are not just hidden. Now I need to create a worksheet to deal with many more students but still link in the same ways to the compiling sheet. 

When I try to insert rows, I get a message saying that I will be shifting "objects off worksheet." 

Does anyone know a quick way to change the usable area of a spreadsheet? I imagine I deleted all rows above 175, so I just want to bring them back! 

Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-23T18:31:59+00:00

    You cannot delete rows or columns from a worksheet.

    You can hide them only.

    Sounds like you have data or simply spaces in cells at bottom of sheet so cannot insert rows above.

    Select the little square at top left intersection of rows and columns then Format>Rows>Unhide.

    Select rows from 175 to bottom and hit Delete to clear them out.  Save workbook.

    You may now decide how many rows you want visible and re-hide those you don't need to view.

    Gord

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-06-23T19:30:16+00:00

    Did you delete rows without looking at contents?

    Might be you should get out the backup copy and try again but do not delete any rows that link to your report sheets.

    Gord

    0 comments
  3. Anonymous
    2016-06-23T18:56:29+00:00

    Thanks for your replies! The unprotect and unhide worked. Thanks so much.

    So now I have a new problem...

    I have 3 reports that link to that first worksheet which went from 136 rows to 533 now. All the formulas linked to that data are now only looking at rows 2-136. Is there any way to change all these more quickly than manually going through each range in each formula?

    UGH.

    Thank you thank you!

    0 comments
  4. Anonymous
    2016-06-23T18:31:32+00:00

    Re: stop me before I sin again.

    You might have limited your workbook by ...

       1. entering data in the last row of the sheet

       2. hiding all rows below row 175

      3.  protecting the workbook.

    So maybe...

      unprotect the workbook

      select all of the cells on the sheet by clicking the top left corner of the worksheet,

      (where the row numbers and column letters intersect)

      right-click a row heading and choose unhide from the popup menu

      remove any data in the last row

    -Or- just copy and paste the 175 rows onto a new worksheet.

    '---

    Jim Cone

    Portland, Oregon USA

    http://jmp.sh/K95N3ee

    0 comments