|
UsedRange.Rows.Count value
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 |
UsedRange.Rows.Count value
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 |
UsedRange.Rows.Count value
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 |
UsedRange.Rows.Count value
Maybe this will help http://www.contextures.com/xlfaqApp.html#Unused
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "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 |
UsedRange.Rows.Count value
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 |
UsedRange.Rows.Count value
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 |
| All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com