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:
[Quoted Text] > 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
>
> "Bob Phillips" wrote:
>
> > 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
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "JohnUK" <JohnUK[ at ]discussions.microsoft.com> wrote in message
> > news:4F3017FF-621D-486B-900E-9D5D0B2E5883[ at ]microsoft.com...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >>
> > >>
> > >> --
> > >> __________________________________
> > >> HTH
> > >>
> > >> Bob
> > >>
> > >> "JohnUK" <JohnUK[ at ]discussions.microsoft.com> wrote in message
> > >> news:3ADFE952-B7C9-4EB0-B6F3-2058608BE3E6[ at ]microsoft.com...
> > >> > 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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
|