Delete Rows
 

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

Delete Rows
 

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

--
Gary''s Student - gsnu200837

Delete Rows
 

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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200837

Delete Rows
 

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.
--
Gary''s Student - gsnu200837


"JohnUK" wrote:

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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200837

Delete Rows
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200837


"JohnUK" wrote:

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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200837

Delete Rows
 

Thank you for the feedback!
--
Gary''s Student - gsnu200837


"JohnUK" wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200837


"JohnUK" wrote:

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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200837