Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John

Always best to fully explain.

Say col B??
clear all rows from 11 to 30 if 30 is the last row, or 29 if 30 is the last
row
OR, clear only blanks in that area

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John

Thanks Don,
Because I have formulas that run down the sides and in between Values
(Values can vary between 1000 to 2800 rows and the formulas run down to 3000
rows, I need the code to delete (All) the rows from 10 rows below the values
that sits in column "K".
Reason I need the code:
Template picks up data from another workbook, then saves as something else,
the code then tidies up (Deletes excess rows) close, re-opens to start the
process over again. I have tried the delete one row at a time option but end
up waiting forever. By using the Clear instead of delete, made it a bit
faster but the file is still left with Ranges that run down to the 3000th
row, thats why I need the code to delete in bulk.
Regards
John


"Don Guillett" wrote:

Always best to fully explain.

Say col B??
clear all rows from 11 to 30 if 30 is the last row, or 29 if 30 is the last
row
OR, clear only blanks in that area

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John

Try something like the following:

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

Set FoundCell = ActiveSheet.UsedRange.Find("abc")
If FoundCell Is Nothing Then
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(11, 1), LastCell).EntireRow.Delete
End With
End Sub

Change WHAT_COLUMN to the column in which the specified value ("abc"
in this example) is to be found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 14 Jan 2009 10:15:01 -0800, JohnUK
wrote:

Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John

Hi Chip, Tried your code, but still not what I need. See my previous post.
Many thanks though. Kind Regards, John

"Chip Pearson" wrote:

Try something like the following:

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

Set FoundCell = ActiveSheet.UsedRange.Find("abc")
If FoundCell Is Nothing Then
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(11, 1), LastCell).EntireRow.Delete
End With
End Sub

Change WHAT_COLUMN to the column in which the specified value ("abc"
in this example) is to be found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 14 Jan 2009 10:15:01 -0800, JohnUK
wrote:

Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John

Hi Chip,
I am having another go at this.
I have modified your code slightly:

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
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(2000, 1), LastCell).EntireRow.Delete
End With
End Sub

It works appart from it deletes the last row of data.
Is there some sort of an offset (Have I got that right) so that it wont
delete the last so many rows below the last value (10 rows for example)?

Many thanks

John





"Chip Pearson" wrote:

Try something like the following:

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

Set FoundCell = ActiveSheet.UsedRange.Find("abc")
If FoundCell Is Nothing Then
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(11, 1), LastCell).EntireRow.Delete
End With
End Sub

Change WHAT_COLUMN to the column in which the specified value ("abc"
in this example) is to be found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 14 Jan 2009 10:15:01 -0800, JohnUK
wrote:

Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John