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
(E-Mail Removed)
"JohnUK" <(E-Mail Removed)> wrote in message
news:A2A42EE1-A889-47DA-B242-(E-Mail Removed)...
> 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, that’s 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
> (E-Mail Removed)
> "JohnUK" <(E-Mail Removed)> wrote in message
> news:A2A42EE1-A889-47DA-B242-(E-Mail Removed)...
> > 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
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
>