Hi NickHK
Why Need to add 2 to get the UsedRange in my case ?

TotalLine = mRaw.UsedRange.Rows.Count + 2 '<<== this

Also, current worksheet usedrange return 100. how to reset to 1 ?

Now, I am using below method.
Set gShErr =
Application.Workbooks(site.Main.getVFile).Workshee ts("Not_found")

If gShErr.UsedRange.Rows.Count 1 Then
'~~MsgBox "gshErr count " &
VBA.str(gShErr.UsedRange.Rows.Count)
gShErr.Rows("2:" & gShErr.UsedRange.Rows.Count).Delete
Shift:=xlUp
End If

But, one of worksheet can not about to reset to 1.

NickHK wrote:
Not sure I follow, but .Rows.Count will give the number of rows in the
UsedRange, whilst .Row will tell you where it starts.
So .Row+.Rows.Count-1 will tell you it ends.
Is that what you mean ?

And yes, UsedRange.Rows.Count is never < 1.
(Although there are reports of situations where it does not return the
correct range.)

NickHK

"moonhk" wrote in message
ups.com...
Dear Reader

I using UsedRange.Rows.Count to find UsedRange row number.
Sometime, I need adjust the value. Do you know why ?
How to delete all the rows in worksheet. As a result
UsedRange.Rows.Count return 1 by VBA ?



'~~ Fix mfgpro CC column
TotalLine = mRaw.UsedRange.Rows.Count + 2 '<<== this
For Each cell In mTarget.Range(gcTHA.DNS.MfgproCCAddr & "3:" _
& gcTHA.DNS.MfgproCCAddr & TotalLine)
If IsNumeric(cell.Value) And cell.Offset(-1, 0).Value 0 Then
tmpKey = "'" + leadingZero(cell.Value, 3)
cell.Value = tmpKey
End If
Next

TotalRows = mTarget.UsedRange.Rows.Count + 1 '<<== this

For cnt = 3 To TotalRows
With mTarget
.Range(gcTHA.DNS.SubTotalAddr & cnt).Formula = _
"=round(" & gcTHA.DNS.CurrAmtAddr & cnt & ",2)"
.Range(gcTHA.DNS.MarkupAddr & cnt).Value = 0
.Range(gcTHA.DNS.BT_RCTAddr & cnt).Value = 0
.Range(gcTHA.DNS.VAT_CSAddr & cnt).Value = 0
'~~ Copy the Amount value
.Range(gcTHA.DNS.AmountAddr & cnt).Value = _
.Range(gcTHA.DNS.CurrAmtAddr & cnt).Value
'~~ Round up Amount Value
.Range(gcTHA.DNS.CurrAmtAddr & cnt).Formula = _
"=Round(" & gcTHA.DNS.AmountAddr & cnt & ",2)"

'~~.Range(gctwa.DNS.LinePropAddr & cnt).Value = "Header"
.Range(gcTHA.DNS.ProjectCodeAddr & cnt).Value =
gcTHA.Raw.Default_ProjCode
'~~ .Range(gctwa.DNS.SupplierAddr & cnt).Value =
gctwa.Raw.Default_SupplierAddr
If .Range(gcTHA.DNS.MfgproACCAddr & cnt) & "-" &
.Range(gcTHA.DNS.MfgproCCAddr & cnt) = _
gcTHA.Raw.UplifeAccAddr Then
.Range(gcTHA.DNS.DescriptionAddr & cnt) =
gcTHA.Raw.UplifeAccDescAddr
End If
End With
Next