In Microsoft Excel, how do I find out what range of cells is actually being used in a worksheet?

Question

In other words, what range of cells would I have to look at to see all the non-empty cells? Excel clearly knows this, because the horizontal and vertical scroll bars are sized according to the cells you've used. Is there a worksheet property, or a function, which will return this range? Or a short sequence of keystrokes that will highlight the used portion of the worksheet?

Answer 1

Don't need VBA for this:

1. Edit | Go To... | Special... (F5 opens Go To also)
2. Check Last Cell
3. Click OK
4. Ctrl + Shift + Home

Your entire "used" range will be selected.

Answer 2

When you know VBA, it's simple. Just run a loop over the sheet
Do While Not IsEmpty(.cells(1, currentCell))

currentCell = currentCell + 1
Loop
This you do in both directions.
The problem is, if there is an empty cell in the between.
So just look for a certain range
lets say
For iRow = 1 to 1000
For iCol = 1 to 100
If Not IsEmpty(iRow, iCol) then
lastRow = iRow
lastCol = iCol
End if
Next iCol
Next iRow
And the end you have the last filled Cell


Helmut

Answer 3

The best thing I know is to select your data then go to Data, Filter, AutoFilter. Then click on the down arrow at the right side of each column and pick NonBlanks for the columns that you want to see no empty cells in.

Answer 4

if you understand how to manually style the formulation it must be the equal. appear for the little components image within the menu i am not definite how 2011 is. 2007 is the most up-to-date Office variation i have used.