Auto remove row depending on cell value

XpbX

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    27.1 KB · Views: 10

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
For r = 195 To Range("A1").Value Step -1
    If Cells(r, "c") = 0 Then
        Rows(r).EntireRow.Delete
    End If
Next r
 

XpbX

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

VBA Code:
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:

VBA Code:
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
So what exactly is in cell A1?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,162
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top