Delete Rows

J

JohnUK

Hi, I am in need of a piece of code that can delete all rows downwards from
10 rows below a value in column K
For example, If the cell K2050 contained a value, I want the code to delete
all rows from Cell K2060 downwards. Columns either side of the Value have
formulas that run down to 3000 rows and that is why I need to concentrate on
column K.
Help greatly appreciated
Kind Regards
John
 
G

Gary''s Student

Sub RowKiller()
Dim r1 As Range, r2 As Range
Dim n As Long
n = 2050
m = n + 10
Set r1 = Range("K" & n)
Set r2 = Range("K" & m & ":K" & Rows.Count)
If r1.Value <> "" Then
r2.EntireRow.Delete
End If
End Sub
 
J

JohnUK

Hi Gary, Many thanks for your help. What I should have mentioned was that the
values fluctuate between 1000 and 3000 rows, whereas your code is specific.
Is there some way of changing the code so that it will delete from the tenth
row below the value where ever it ends in column K?
This piece of code is one that Chip Pearson kindly done for me some time ago
that I modified slightly, but I still cant get it to work for my needs
because it ends up deleting the last row of data (If it helps)

Sub AAA()
Const WHAT_COLUMN = "k"
Dim LastCell As Range
Dim FoundCell As Range

Set FoundCell = ActiveSheet.UsedRange.Find("k")
If FoundCell Is Nothing Then
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(3000, 1), LastCell).EntireRow.Delete
End With
End Sub

Thanks
John
 
G

Gary''s Student

This version will first locate the last filled in in column K, go do the 10
rows and the do the delete:

Sub RowKiller()
Dim r1 As Range, r2 As Range
Dim n As Long
n = Cells(Rows.Count, "K").End(xlUp).Row
m = n + 10
Set r1 = Range("K" & n)
Set r2 = Range("K" & m & ":K" & Rows.Count)
If r1.Value <> "" Then
r2.EntireRow.Delete
End If
End Sub

So if K4 is the last filled cell in column K, then rows 14 on down will be
deleted.
 
J

JohnUK

Gary - You are a star *
Many thanks, it worked a treat. You dont know how long I have been putting
up with this problem!!
Take care
Best Regards
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top