Delete records using excel Macro
 

Tweaked routine from one of the others, but forgot to note who, so apologies to
someone. :-)
Assumes last row can be determined from Col A entries.

Sub DelRowsIf()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1

If StrComp(Cells(r, "A"), "program", vbBinaryCompare) = 0 _
Or StrComp(Cells(r, "A"), "-----", vbBinaryCompare) = 0 _
Or Cells(r, "F").Value = "" Then
Rows(r).Delete
Else: End If
Next r

Application.ScreenUpdating = True

End Sub

Note, you don't need to check for any completely blank rows, as if Col F is
blank it will be deleted anyway, and obviously in every completely blank row,
Col F is blank anyway.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Lillian" wrote in message
...
I have 60,000 records of excel spreed sheet, insite I
need to delete some of records, the condition is
following:
if columnA has "program", this record will be delete
if columnA has "-----", this record will be delete
if entire row is space, this record will be delete
if columnF is space, this record will be delete.

how can I write the macro in excel spreed sheet.
thanks for all the help.


Lillian


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003

Delete records using excel Macro
 

Just in case you can't rely on A to determine last row

Sub DelRowsIf()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For r = LastRow To 1 Step -1

If StrComp(Cells(r, "A"), "program", vbBinaryCompare) = 0 _
Or StrComp(Cells(r, "A"), "-----", vbBinaryCompare) = 0 _
Or Cells(r, "F").Value = "" Then
Rows(r).Delete
Else: End If
Next r

Application.ScreenUpdating = True

End Sub


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
Tweaked routine from one of the others, but forgot to note who, so apologies
to
someone. :-)
Assumes last row can be determined from Col A entries.

Sub DelRowsIf()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1

If StrComp(Cells(r, "A"), "program", vbBinaryCompare) = 0 _
Or StrComp(Cells(r, "A"), "-----", vbBinaryCompare) = 0 _
Or Cells(r, "F").Value = "" Then
Rows(r).Delete
Else: End If
Next r

Application.ScreenUpdating = True

End Sub

Note, you don't need to check for any completely blank rows, as if Col F is
blank it will be deleted anyway, and obviously in every completely blank row,
Col F is blank anyway.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Lillian" wrote in message
...
I have 60,000 records of excel spreed sheet, insite I
need to delete some of records, the condition is
following:
if columnA has "program", this record will be delete
if columnA has "-----", this record will be delete
if entire row is space, this record will be delete
if columnF is space, this record will be delete.

how can I write the macro in excel spreed sheet.
thanks for all the help.


Lillian


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003