Excel 2000 VBA - Delete Rows That Meet Criteria
 

I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.

Excel 2000 VBA - Delete Rows That Meet Criteria
 

You don't need VBA. Just do a filter (DataFilterAutofilter) and select a
custom value of Blank and not Cred, and then just delete the visible rows.

--
HTH

Bob Phillips

"William Horton" <William wrote in message
...
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row
that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do
this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.

Excel 2000 VBA - Delete Rows That Meet Criteria
 

Try this macro

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

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 = "" And _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"William Horton" <William wrote in message
...
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.

Excel 2000 VBA - Delete Rows That Meet Criteria
 

Oops

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this macro

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

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 = "" And _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"William Horton" <William wrote in message
...
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.

Excel 2000 VBA - Delete Rows That Meet Criteria
 

THANKS RON! Your code did exactly what I needed and was very much appreciated.

"Ron de Bruin" wrote:

Oops

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this macro

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

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 = "" And _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"William Horton" <William wrote in message
...
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.

Excel 2000 VBA - Delete Rows That Meet Criteria
 

Thanks for the feedback

Other examples you can find here
http://www.rondebruin.nl/delete.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"William Horton" wrote in message
...
THANKS RON! Your code did exactly what I needed and was very much appreciated.

"Ron de Bruin" wrote:

Oops

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this macro

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

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 = "" And _
.Cells(Lrow, "A").Value < "Cred" And _
.Cells(Lrow, "A").Value < "PTUB" And _
.Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"William Horton" <William wrote in message
...
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range
called Company_Code (a range for rows in column "A" up to the last row that
has data) that are either "Blank" or do NOT contain the values "Cred",
"PTUB", "CMEX". Can somebody please provide me with the VBA code to do this?

I've played around with this forever and can't get it to work. Also, I
don't know if I am doing it the most efficient way.

Any help would be MUCH appreciated.