Select a large data set which has blank rows and columns without using the Shift and arrow keys

{2 Comments}

Assume a large data set spanning many rows and columns.  In the data set are entirely blank rows and columns.  As an example, let's say data is in range A2:G5000, then from A5005:J87634 and then from A87636:F92345.  Furthermore, assume that columns H and I are completely blank.  Data in the filled up cells can be number, formulas, text, alphanumeric strings etc..  The objective is to select the range A2:J92345.  Since there are entire blank rows and columns, selection with Ctrl, Shift and arrow keys or Ctrl+A will fail.

If one attempts to select with the Shift and arrow keys, it will take just way too long to select the desired range.  To select range A2:J92345, try this approach

1. Go to the last available cell.  In Excel 2007 and higher versions, that will be XFD1048576 and in Excel 2003 and lower versions, it will be IV65536.  One can directly go to these cells by typing in the cell reference in the Reference to box of Ctrl+G and clicking on OK.
2. Once on the last cell, press Ctrl+F.  In the Find what box, type *
3. Click on Options and ensure that Search is set to "By Row"
4. Press the Shift key and click on Find next.  This basically forces a backward search.  Make a note of the row number of the active cell.  In my example above, it should be 92345.
5. Repeat steps 1-2 above
6. Click on Options and ensure that Search is set to "By Row"
7. Press the Shift key and click on Find next.  This basically forces a backward search.  Make a note of the column alphabet of the active cell.  In my example above, it should be J
8. Press Ctrl+G and in the reference box, type A2:J92345 and click on OK.

RSS 2 Comments…

 Share your views
  1. Hi Ashish, interesting method that I haven't thought about before. However, wouldn't it be quicker to simply do Ctrl+End? It gets you to the same spot, which then allows you to continue wiht Ctrl + G as per Step 8.

    • No, the two are different. The Ctrl+End or Ctrl+Shift+End recognizes even formatted cells. Try this

      1. Enter data in range C3:C5
      2. Enter data in range E7:E8
      3. Apply colour or formatting (Bold Italic etc.) or conditional formatting to cell G10. Do not type anything in cell G10

      My objective is to select the range of data i.e. range C3:E8. By my method, you will be able to select range C3:E8. But if you press Ctrl+End from cell C3, then cell G10 will become the active cell. Likewise if you press Ctrl+Shift+End from cell C3, then range C3:G10 will get highlighted.

      Also, surprisingly, if you clear the formatting or colour from cell G10 and press Ctr+End or Ctrl+Shift+End, it will still select C3:G10.

      Hope this clarifies.

Leave a Comment

Your email address will not be published.

*

*