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]

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).

Yup, I did this after realizing I was losing two rows:

Sub DeleteRowsBelow()
    Dim r As Long
    r = Range("D1").Value
    Rows(r + 2 & ":" & Rows.Count).Delete
End Sub

Just your initial solution has allowed me to expand this greatly in other sheets using similar macros. Thanks again, you made my week!

 

[Joe4]

You are welcome.

Note if you wanted to use the other solution (if you like the shorter one), just add 2 there too, i.e.

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