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.
Select a large data set which has blank rows and columns without using the Shift and arrow keys
{ 2 Comments }