Auto remove row depending on cell value

[XpbX]

Hi, I am working on a worksheet for our sale rep, with list of equipment which the macro removes all the rows that has no Qty in it
So far I use this:

Dim lastrow As Long, r As Long

lastrow = Cells(Rows.Count, "G").End(xlUp).Row

For r = 195 To 14 Step -1
If Cells(r, "c") = 0 Then
Rows(r).EntireRow.Delete
End If
Next r

Which I copied from one thread here and adapted it to my needs. ( I have to admit I don't understand the whole thing though)

It works fine, Qty is in column C, and it deletes everything that needs to be gone,

My main problem is that at the end of all the items there is notes and sales stuff and I which I could start removing not from a row number but from a certain cell I call "end" for now. Which is between the items and the sales notes.
I want the reps to be able to redo the macro that deletes row if they adjust values (as they often renew contracts with some items not needed to be charged again)

So basically, my question is how can we remove rows starting from a row that is not a fixed row number? but based on a cell value
is this even possible.
Thank you for your help.

 

[XpbX]

How can you loop from "Estimate" to 14?

The way you have written the code, you are telling it to take whatever is in cell A1 and loop from that number down to 14.
So if you do not have a number in cell A1, it will not work.

I think you are a bit confused, so let's take a few steps back here for a minute.
Your ending row is dynamic (meaning that you cannot hard-code it, as it changes).
That means we must come up with a dynamic solution.

So we have a few options:
1. To pull from a variable somewhere
- It can be a cell, like A1, but that means that cell always has to contain the correct value). Typically, this would be some sort of formula/calculation in the cell.
- It can be a variable that is calculation in VBA
2. To dynamically tell it to start from the last populated cell in some particular column. If you choose to go this route, you MUST choose a column that will always be populated for data. in every row that you want your loop to hit.

So, if you can just explain, in plain English the "logic" you want us to use to dynamically locate which is the last row to use in the loop, we can help you with that.
But w need you to clearly provide that information to us.

Eureka!!!

What I did is instead of looking to last row on row C, It will look in row J, where there is never anything written there in the last section.

Thanks for your help sir!

 

[Joe4]

You are welcome.
Glad we got it figure out.