Hi NickHK

I try below coding , need add mTarget.Rows("1").Delete. make sure that
1 line also deleted.

If mTarget.UsedRange.Rows.Count 0 Then
mTarget.Rows("1:" & mTarget.UsedRange.Rows.Count).Delete
Shift:=xlUp
mTarget.Rows("1").Delete
'~~ MsgBox "UsedRange Rows Count" &
str(mTarget.UsedRange.Rows.Count)

End If

I will try your coding.

NickHK wrote:
You cannot set/reset the Usedrange, it is Read-Only

Also you are hard coding the row 2, which may/may not be involved.
I'm not sure what you are trying to do, but

With gShErr.UsedRange
.select
.delete
msgbox .rows.count
End With

NickHK

"moonhk" wrote in message
oups.com...
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