Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm

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
.ScreenUpdating = False
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
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "ron"
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

__________________________________________________ __

Here is my situation and my questions:

In Column A on my spreadsheet, there is data like this:


AR3303
AR4055
Subtotal
Blank Cell (and row)
Blank Cell (and row)
AR9999
DP3838
DP3923
DP3932


What I want to do is delete all rows that do NOT contain the DP in the
first cell (Column A cell). I think I could do this somehow using a
LEFT function and obviously an IF NOT function somehow, but I'm having
problems tweaking it to do it this way.

Can anybody help?

Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 2 Step -1 'Assumes Header Row
x = Cells(i, 1).Value
If Left(x, 2) < "DP" Then
Cells(i, 1).EntireRow.Delete
End If
Next

"Zarlot531" wrote:

Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm

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
.ScreenUpdating = False
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
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "ron"
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

__________________________________________________ __

Here is my situation and my questions:

In Column A on my spreadsheet, there is data like this:


AR3303
AR4055
Subtotal
Blank Cell (and row)
Blank Cell (and row)
AR9999
DP3838
DP3923
DP3932


What I want to do is delete all rows that do NOT contain the DP in the
first cell (Column A cell). I think I could do this somehow using a
LEFT function and obviously an IF NOT function somehow, but I'm having
problems tweaking it to do it this way.

Can anybody help?

Wow you're a genius ... thanks a lot!


On Apr 28, 3:16 pm, JLGWhiz wrote:
Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 2 Step -1 'Assumes Header Row
x = Cells(i, 1).Value
If Left(x, 2) < "DP" Then
Cells(i, 1).EntireRow.Delete
End If
Next



"Zarlot531" wrote:
Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm

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
.ScreenUpdating = False
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
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "ron"
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

__________________________________________________ __

Here is my situation and my questions:

In Column A on my spreadsheet, there is data like this:

AR3303
AR4055
Subtotal
Blank Cell (and row)
Blank Cell (and row)
AR9999
DP3838
DP3923
DP3932

What I want to do is delete all rows that do NOT contain the DP in the
first cell (Column A cell). I think I could do this somehow using a
LEFT function and obviously an IF NOT function somehow, but I'm having
problems tweaking it to do it this way.

Can anybody help?- Hide quoted text -

- Show quoted text -

Here is my preliminary code. I've changed it around a little from
what you've written, but could you explain what the "To 1 Step -1"
really means? I changed it from 2 to 1 because I have no header, and
I asusme the -1 is referring to the footer.

But one problem I'm having is that I run the code and it works
perfectly except for the fact that it stops reading too soon. For
example, there will be a few more lines left (rows who don't have data
starting for five or six columns over) at the very end. If I move the
lines at the end over to the very first column, then it reads it and
is fine, or if there happens to be a row that is over far left enough
at the end, then everything is fine. Which part of this code controls
how far over the program reads to tell when the rows have stopped?

Thanks...

Sub DelRw()
Dim lstRw
Dim i
Dim x
Dim CalcMode
With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 1 Step -1
x = Cells(i, 3).Value
If Left(x, 4) < "2745" Then
Cells(i, 3).EntireRow.Delete
End If

Next
With Application
..ScreenUpdating = True
..Calculation = CalcMode
End With

End Sub















On Apr 28, 3:16 pm, JLGWhiz wrote:
Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 2 Step -1 'Assumes Header Row
x = Cells(i, 1).Value
If Left(x, 2) < "DP" Then
Cells(i, 1).EntireRow.Delete
End If
Next



"Zarlot531" wrote:
Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm

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
.ScreenUpdating = False
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
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "ron"
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

__________________________________________________ __

Here is my situation and my questions:

In Column A on my spreadsheet, there is data like this:

AR3303
AR4055
Subtotal
Blank Cell (and row)
Blank Cell (and row)
AR9999
DP3838
DP3923
DP3932

What I want to do is delete all rows that do NOT contain the DP in the
first cell (Column A cell). I think I could do this somehow using a
LEFT function and obviously an IF NOT function somehow, but I'm having
problems tweaking it to do it this way.

Can anybody help?- Hide quoted text -

- Show quoted text -

The step -1 is to walk from the bottom of the column to the top.
Since you are deleting rows, and the default is to shift up, it is
better to start at the bottom and work toward the top. Otherwise
it would skip rows as it worked its way down.

The last row (lstRw) is calculated in the code you are using based
on column "A". You can do one of two things:

1- Change lstRw = Cells(Rows.Count, ?).End(xlUp).Row to replace
the question mark with whichever column you have the most data in or

2- Chage to lstRw = Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCel l).Row

Either of the two methods should then cover all of the rows with data in them.

Since you are only searching a single column, this particular code does not
take the length of the row into consideration except for the delete command
where the entire row is deleted.

"Zarlot531" wrote:

Here is my preliminary code. I've changed it around a little from
what you've written, but could you explain what the "To 1 Step -1"
really means? I changed it from 2 to 1 because I have no header, and
I asusme the -1 is referring to the footer.

But one problem I'm having is that I run the code and it works
perfectly except for the fact that it stops reading too soon. For
example, there will be a few more lines left (rows who don't have data
starting for five or six columns over) at the very end. If I move the
lines at the end over to the very first column, then it reads it and
is fine, or if there happens to be a row that is over far left enough
at the end, then everything is fine. Which part of this code controls
how far over the program reads to tell when the rows have stopped?

Thanks...

Sub DelRw()
Dim lstRw
Dim i
Dim x
Dim CalcMode
With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 1 Step -1
x = Cells(i, 3).Value
If Left(x, 4) < "2745" Then
Cells(i, 3).EntireRow.Delete
End If

Next
With Application
..ScreenUpdating = True
..Calculation = CalcMode
End With

End Sub















On Apr 28, 3:16 pm, JLGWhiz wrote:
Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 2 Step -1 'Assumes Header Row
x = Cells(i, 1).Value
If Left(x, 2) < "DP" Then
Cells(i, 1).EntireRow.Delete
End If
Next



"Zarlot531" wrote:
Here is the model of the code I'm using -- this come from
http://www.rondebruin.nl/delete.htm

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
.ScreenUpdating = False
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
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "ron"
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

__________________________________________________ __

Here is my situation and my questions:

In Column A on my spreadsheet, there is data like this:

AR3303
AR4055
Subtotal
Blank Cell (and row)
Blank Cell (and row)
AR9999
DP3838
DP3923
DP3932

What I want to do is delete all rows that do NOT contain the DP in the
first cell (Column A cell). I think I could do this somehow using a
LEFT function and obviously an IF NOT function somehow, but I'm having
problems tweaking it to do it this way.

Can anybody help?- Hide quoted text -

- Show quoted text -