UsedRange and CurrentRegion
 

Hi,
I need to know realy last row and column, which contains data. I tried to
use UsedRange properties of object worksheet, but this is not what I want in
all real cases:

Sheets("Sheet1").usedrange.rows.count

UsedRange.rows.count properties count also rows only with some formatting
and with no data. Then I tried to use:

Sheets("Sheet1").usedrange.CurrentRegion.rows.coun t

and seems to work OK. Do the last code example find realy last row or column
or count of them in all cases, or is there some better way to get this always
?

Thanks for answer.
Tomas M.

UsedRange and CurrentRegion
 

As you have discovered, UsedRange is the area of the sheet that has been
'Used' for any purpose, since it was last saved. CurrentRegion is the area
of contiguous cells surrounding the ActiveCell, that have data. You will
need to select whichever is appropriate for your needs at the time.
Mike F
"Tomas M." wrote in message
...
Hi,
I need to know realy last row and column, which contains data. I tried to
use UsedRange properties of object worksheet, but this is not what I want
in
all real cases:

Sheets("Sheet1").usedrange.rows.count

UsedRange.rows.count properties count also rows only with some formatting
and with no data. Then I tried to use:

Sheets("Sheet1").usedrange.CurrentRegion.rows.coun t

and seems to work OK. Do the last code example find realy last row or
column
or count of them in all cases, or is there some better way to get this
always
?

Thanks for answer.
Tomas M.

UsedRange and CurrentRegion
 

Current region will expand out to the first entirely blank row and column
(or edge of spreadsheet). So if you data is all in one block with no
embedded entirely blank rows or columns, it should give you the information
you want.

Another

lastrow = cells(rows.count,1).End(xlup).Row

this will only check values in column A (or the column you specify by
changing the 1). So if you have a specific column that you know will have a
value in the last filled row, then you can use this method as an
alternative.

--
Regards,
Tom Ogilvy

"Tomas M." wrote in message
...
Hi,
I need to know realy last row and column, which contains data. I tried to
use UsedRange properties of object worksheet, but this is not what I want
in
all real cases:

Sheets("Sheet1").usedrange.rows.count

UsedRange.rows.count properties count also rows only with some formatting
and with no data. Then I tried to use:

Sheets("Sheet1").usedrange.CurrentRegion.rows.coun t

and seems to work OK. Do the last code example find realy last row or
column
or count of them in all cases, or is there some better way to get this
always
?

Thanks for answer.
Tomas M.

UsedRange and CurrentRegion
 

OK, if I understand this, UsedRange is maximally arrea, which I modified
anyway, inluded blank rows and columns, which are have some cells with some
formatting. And UsedRange.CurrentRegion included only that maximum area of
worksheet.UsedRange, when some data is in rows and columns through whole
worksheet, even when the data are in many areas and between that areas are
the rows or columns with no data. Other cells (out of area
UsedRange.CurrentRegion) can contain only some formating, but no data. If
this is correct expectation, it is what I want.

Tomas M.

UsedRange and CurrentRegion
 

No. CurrentRegion is specific to a single area. (an area in this instance
is filled cells that do not have an entirely blank row or column across
their width or height.)

If you used a cell in Area1 and did Currentregion, it would return Area1

UsedRange reflects the cells that Excel stores information about (it is
always a single rectangular range). If Excel does not need to store
information about cells, it does not (so while you see 65536 rows, those
past the used range are vitual until needed). So usedrange will never be
less that the total range of filled cells, but it may be larger (the problem
you are having).

If you want to find the "RealLastCell with data:"

In a thread subject Change What Excel Believes Is The Last Cell
In Microsoft.Public.Excel.Programming on October 15, 1999

This was posted by John Green
"The following code will find the last row and last column that contain data
on the active worksheet:"

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub

[ the cell selected is at the intersection of the last row and last column
and may be empty, so you can use each result independently]

This is a version posted by David Chazin with some additional error checking

Sub LastCell_Select()
'David Chazin 05-Mar-1999
'Selects the cell at the bottom-right corner of the used area
' of the currently selected worksheet.
Range(LastCell_Get()).Select
End Sub

Function LastCell_Get() As String

'David Chazin -- September 1999.
'Returns the Address (e.g. "$AW$235") of the cell at the
' bottom-right corner of the used area of the currently
' selected worksheet. Returns "" if the worksheet is empty.
' Thanks to Dave Braden for the idea of trapping for an
' empty worksheet. (This is not the way he would implement
' it, but the idea itself is his).

On Error GoTo LastCell_Get_ErrorHandler

If Range("A1").SpecialCells(xlLastCell).Value < "" Then
LastCell_Get = Range("A1").SpecialCells(xlLastCell).Address()
Else
LastCell_Get = _
Cells(Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByRows, xlPrevious).Row, _
Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByColumns, xlPrevious).Column).Address()
End If
Exit Function
LastCell_Get_ErrorHandler:

If Err.Number = 91 Then
'If the worksheet is empty....
LastCell_Get = ""
Else
Call MsgBox("Error #" & Err.Number & " was generated by " & _
Err.Source & ": " & Err.Description, vbOKOnly + vbExclamation, _
"LastCell_Get()", Err.HelpFile, Err.HelpContext)
End If

End Function


Regards,
Tom Ogilvy

"Tomas M." wrote in message
...
OK, if I understand this, UsedRange is maximally arrea, which I modified
anyway, inluded blank rows and columns, which are have some cells with
some
formatting. And UsedRange.CurrentRegion included only that maximum area of
worksheet.UsedRange, when some data is in rows and columns through whole
worksheet, even when the data are in many areas and between that areas are
the rows or columns with no data. Other cells (out of area
UsedRange.CurrentRegion) can contain only some formating, but no data. If
this is correct expectation, it is what I want.

Tomas M.

UsedRange and CurrentRegion
 

OK, now it is cleared. I understand the diferencies. Thanks a lot.

Tomas M.