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.

 

[Joe4]

Welcome to the Board!

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

Where/how is this cell to be identified?

If it is a set cell (like A1), we could do it like this:

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

 

[XpbX]

Thanks,

From what I understand of the code you are proposing, it will start at row 195 the go up and stop at A1, right?

But I need the starting row to be variable, since the first time I hit the macro, I will lose 40 rows or such, then I wanna be able to relaunch the macro, but the first row to delete won't be 195 anymore, it will be let's say 155. My initial thought is to put a value in that cell so that the macro search for this as being the first one to look for to delete. But it might just be a different approach completely.

I really appreciate the help you put in...

 

[Joe4]

From what I understand of the code you are proposing, it will start at row 195 the go up and stop at A1, right?

No, that is not correct. It will stop at the value that is contained in cell A1.

Your initial question wasn't quite clear to me. I wasn't sure if you wanted the higher number or the lower number variable. So I was showing you how to make the lower number variable (based on whatever number is entered into cell A1).

Also, I wasn't sure where exactly this "variable" is coming from. Is it pulling from some cell, or is it to be calculated somehow (if calculated, please explain how we are to derive it).

If you simply want to start at the last populated cell in column c, you could do this:

Dim lr as Long

'Find last cell in column C with data
lr = Cells(Rows.Count, "C").End(xlUp).Row

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

 

[XpbX]

Dim lr as Long

'Find last cell in column C with data
lr = Cells(Rows.Count, "C").End(xlUp).Row

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

This works, but I'll have to lock all "C" cells in the last section of the sheet so that nothing gets written there. It should be a good compromise for now to get this thing started.
Is there a way to phrase the last row value so it is the value in a cell?(like I said right now it's 195 but it is a variable once I delete a bunch of rows.)

as for:

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

I did:

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

and copied cell value A1 to A195
and I get the spinning blue thing going on infinitely..


I could not get this to work by replacing 195 to the range.("A1").value

Once again, I really appreciate the time you put on this to help me...

 

[Joe4]

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

There should NOT be a space between Range and ("A1").

 

[XpbX]

There should NOT be a space between Range and ("A1").

I don't know why it shows a space, even if I copy and paste it, it's not there.

 

[Joe4]

So what exactly is in cell A1?

 

[XpbX]

So what exactly is in cell A1?

the cell says "Estimate:"

 

[Joe4]

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.