Delete rows based on cell value

[swartzfeger]

Let's say I have 90 rows of data, A2:A91, and I have a cell C2 that is always changing, telling me how many rows I should have, let's say C2 = 50.

How do I write the VBA to tell it to delete any row after A51?

The number imported into A will always be changing, as will the actual number of rows needed in C2, so I'm looking for an easy way to make this idiot proof for the user without having to sort and filter.

Thank you so much for any pointers!

 

[swartzfeger]

So something like this:

Book1.xlsm
	A	B	C	D
1	data		total rows	5
2	ABC
3	DEF
4	GHI
5	JKL
6	MNO
7	PQR
8	STU
9	VWX
10	YZ
11	123
12	456
13	789
Sheet1

The script would then automatically delete rows 7-13 based on D2:

Book1.xlsm
	A	B	C	D
1	data		total rows	5
2	ABC
3	DEF
4	GHI
5	JKL
6	MNO
Sheet1

Thanks for any pointers!

 

[swartzfeger]

So I need to do something like

Range("a7:a13").EntireRow.Delete

But I need to figure out how to keep any rows less than <D1 and delete anything greather than >D1

I could do something like this:

Sub DeleteAllBelow()
Rows(ActiveCell.Row & ":" & Rows.Count).Delete
End Sub

But that would require the user to pay attention, not misclick on the wrong row, etc.

 

[Joe4]

In your original question, you say the number is in C2.
In your follow-up, you see it is in cell D2.
But in your image, you show it in cell D1.
So where exactly is this number?

 

[Joe4]

If it is cell D1, then something like this would work:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Get value from cell D1
    r = Range("D1").Value
    
'   Delete rows
    If lr - r > 1 Then Rows(r + 2 & ":" & lr).Delete
        
End Sub

 

[swartzfeger]

If it is cell D1, then something like this would work:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Get value from cell D1
    r = Range("D1").Value
   
'   Delete rows
    If lr - r > 1 Then Rows(r + 2 & ":" & lr).Delete
       
End Sub

Joe I deeply apologize I tried to go back and edit the post/s to clear up confusion but I was unable. Yes, it's D1.

It worked perfectly, and I can see the logic of what you did -- grab the total range and then lr - r. The problem I had figuring out was how to declare/dim D1 so I could then use it to calculate. Thank you SO MUCH!!!

Could something like this also work?

Sub DeleteRowsBelow()
Rows("D1" & ":" & Rows.Count).Delete
End Sub

Or would I need to declare D1 as a value to be used to determine the row number?

Thanks again, learning this stuff is fun :D

 

[Joe4]

Sub DeleteRowsBelow()
Rows("D1" & ":" & Rows.Count).Delete
End Sub

Or would I need to declare D1 as a value to be used to determine the row number?

If cell D1 contains the first row number of the row you would like to delete, then it would need to look like:

Sub DeleteRowsBelow()
    Rows(Range("D1").Value & ":" & Rows.Count).Delete
End Sub

 

[swartzfeger]

So I just did this and it works too!!!

Sub DeleteRowsBelow()
    dim r as long
    r = Range("D1").Value
    Rows(r & ":" & Rows.Count).Delete
End Sub

Thank you again, Joe!!!

 

[swartzfeger]

If cell D1 contains the first row number of the row you would like to delete, then it would need to look like:

Sub DeleteRowsBelow()
    Rows(Range("D1").Value & ":" & Rows.Count).Delete
End Sub

Ok I just missed your post as I was posting, I like your way better because it's cleaner. Thank you again for all your help.

 

[Joe4]

So I just did this and it works too!!!

Sub DeleteRowsBelow()
    dim r as long
    r = Range("D1").Value
    Rows(r & ":" & Rows.Count).Delete
End Sub

Thank you again, Joe!!!

You are welcome.
Just note that in your original question, the number in that cell was the number of data rows you wanted, so you needed to add 1 to delete the first row after that.
But you also appeared to have a header row, pushing that down one more row, which is why we added 2 in the original code.
Just be aware of those things as you make changes (as the last version of code you marked as the solution would not work on your original question, as it was described).