usedrange doesn't show the right value

[Karin S]

I am trying to write a VBScript where I will first read
all values in an excel spreadsheet and then add a "'" in
the beginning of all cells (those which contains a value
that is). I use UsedRange to check which values that will
be controlled, but the thing is that UsedRange takes more
cells. If I copy only where there are values, and then
paste these values into a new spreadsheet then it is ok,
but I can't do this in the future when excel spreadsheets
will come from here and there and then this script must
know which cells that really contains a value. I did
found a script for VBA on this side:
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;231007 but I didn't get it to work in my VBScript.
Does anyone have any experience with this and give me
some help, then I would be most grateful.

best regards
Karin

 

[Tom Ogilvy]

Actually, the UsedRange is correct in terms of the fact it is defined as the
area which Excel considers to be in use. It isn't defined as the extent of
cells containing values. Here is a routine that will give you the cell at
the bottom right of the rectangular range of values containing values.

Sub ValuesRealLastCell()
Dim RealLastRow As Long, RealLastColumn As Long
With ActiveSheet
RealLastRow = .Cells.Find("*", Range("A1"), , , _
xlByRows, xlPrevious).Row
RealLastColumn = .Cells.Find("*", Range("A1"), , , _
xlByColumns, xlPrevious).Column
.Cells(RealLastRow, RealLastColumn).Select
End With
End Sub


You may need to use the constant values

Sub ValuesRealLastCell()
Dim RealLastRow As Long, RealLastColumn As Long
With ActiveSheet
RealLastRow = .Cells.Find("*", Range("A1"), , , _
1, 2).Row
RealLastColumn = .Cells.Find("*", Range("A1"), , , _
2, 2).Column
.Cells(RealLastRow, RealLastColumn).Select
End With
End Sub