VBA For To Next code with variable To

[hoby14]

Hi All,

I am trying to write code to cycle through rows in an excel spreadsheet and delete the row if the end column is less than 1.
The code does delete the rows correctly however as it is a For x = y to z means that when a row is deleted the z does not reduce.

I tried to fixed this by reducing the z (in this case it is FinalRowFcstSheet) by -1 if a row has been deleted, however the RowNo goes beyond the FinalRowFcstSheet number and seems to continue on till the initial FinalRowFcstSheet value of circa 45,000, I have to stop the code as runs for a very long time.

below is the code is there an easy fix or a different way to achieve what I am trying to do.

Many Thanks,

Mark

Dim FinalRowFcstSheet As Long
Dim RowNo As Long


RowNo = 2


FinalRowFcstSheet = Worksheets("WkOnWkFcst").Range("b65000").End(xlUp).Row
FinalColumnFcst = Worksheets("WkOnWkFcst").Range("IV1").End(xlToLeft).Column



For RowNo = 2 To FinalRowFcstSheet




If Worksheets("WkOnWkFcst").Cells(RowNo, FinalColumnFcst).Value < 0.5 Then
FinalRowFcstSheet = FinalRowFcstSheet - 1
Worksheets("WkOnWkFcst").Rows(RowNo).Delete
RowNo = RowNo - 1

End If


Next RowNo

 

[Fluff]

Hi & welcome to MrExcel
When deleting rows it's better to loop from bottom up, like

For RowNo = FinalRowFcstSheet To 2 Step -1
   If Worksheets("WkOnWkFcst").Cells(RowNo, FinalColumnFcst).Value < 0.5 Then
      Worksheets("WkOnWkFcst").Rows(RowNo).Delete
   End If
Next RowNo

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...o-next-code-with-variable-to.html#post5134704

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.