Delete Rows

[JohnUK]

Hi, I have a worksheet that is constantly changing in range size and need a
piece of code that can delete all rows below the last row containing data in
column K bar 10. I say column k because there are formulas that run down
other columns and column K has just values.
I have been using:

Sub Delete_Rows()
Application.ScreenUpdating = False
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
ElseIf .Cells(Lrow, "F").Value = "row" Then .Rows(Lrow).Delete
End If
Next
End With
End Sub

But proves to be too slow especially when I have to wait for up to a 1000
lines to be deleted
Help greatly appreciated
John

 

[Bob Phillips]

Sub Delete_Rows()
Application.ScreenUpdating = False
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet

Set rng = Range("K" & Firstrow).Resize(Lastrow - Firstrow + 1)
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
With Application
.Calculation = CalcMode
End With
ActiveWindow.View = ViewMode
End Sub

 

[JohnUK]

Hi Bob, Many thanks for your help. I ran your code through but unfortunately
I should have mentioned (sorry) that I have blocks of data separated by blank
rows and your code whilst done a very good job at deleting all rows, it isn’t
quite what I wanted because I only needed the rows deleted from the very last
row with values bar 10
Many thanks anyway - I will still use at some stage in the future
Regards
John

 

[Bob Phillips]

I would then use a technique where I found that last row and delete from
there to the end in one block statement, something like

Rows(firstblankrow & ":" & lastrow - firstblankrow +1).Delete

 

[JohnUK]

Thank you Bob, looks promising.
How can I get this to work by looking at the data in column K + 10 rows down
and then delete the rest of the rows?
John

 

[excelent]

It seem's ur looking for cells with errors to delete in column A
then try this one
Sub tst()
Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).Select
Rem when ur sure use next line to delete
Rem Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

About column F... maby instead of "row" u can put an =N/A error in the rows
u wana delete

"JohnUK" skrev:

 

[JohnUK]

Hi, Many thanks for your input. Idealy, what I need is a piece of code that
deletes all rows in one swoop from the bottom of the spreadsheet to 10 rows
below where data ends in a given column (K)
Regards
John