UsedRange.Rows.Count value

[moonhk]

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

 

[NickHK]

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]

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).Worksheets("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.

 

[Bob Phillips]

Maybe this will help http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[NickHK]

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]

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.