From time to time you might encounter the prompt that appears when Excel thinks you can't add additional columns or rows.
This occurs when Excel considers used range of the worksheet to encompass all columns or rows. In this article I'll describe some techniques you can use to overcome this problem.
Figure 1: It's frustrating when Excel won't allow you to insert columns or rows.
The first, and usually easiest, method is to delete all columns to the right of the active area of your worksheet. If you can't insert rows, delete all rows below the active area of your worksheet.
For instance, assume you have data in columns A through M of your worksheet. To delete the remaining columns, place your cursor in cell N1, and then press Ctrl-Shift-Right. This will take you to the last column of the worksheet, which is column XFD in Excel 2007 or 2010, or column IV in Excel 2003 or earlier. Once you've done so, the cells in row 1 starting from column N through the right should be selected. Right-click on any of the selected cells, choose Delete, Entire Column, and then OK.
Further, let's assume our data goes down to row 28. Place your cursor in cell A29, and then press Ctrl-Shift-Down. This will take you to the last row of the spreadsheet, which is row 1,048,576 in Excel 2007 and 2010, or row 65,536 in Excel 2003 and earlier. Right-click on any of the selected cells, choose Delete, Entire Row, and then OK.
You may now try inserting new columns or rows. If that doesn't work, the next step is to use the Visual Basic Editor to enter a single line of code that will reset the used area of the spreadsheet:
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.