| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, delete rows that meet criteria
I have a project coming up that one aspect will require a macro to remove
certain rows in a spreadsheet that contain detailed product information that really isn't needed. Basically I want to turn a detailed product listing into a summarized product listing. Example of what I will have is below. Will start with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 150A MCB 120Y/208V 02 1 B Panelboard (NQOD TYPE) 200A MCB 120Y/208V 03 1 C Panelboard (I-LINE TYPE) 400A MCB 120Y/208V Want to end up with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 02 1 B Panelboard (NQOD TYPE) 03 1 C Panelboard (I-LINE TYPE) Since the line number appears in a specific cell, lets say B2 then basic logic is that is cell B2 is empty, delete row B. Let me know what you think. Thanks, Scott |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro, delete rows that meet criteria
Hi Scott,
This should work. Sub DelRows() Dim Iloop As Double Dim NumRows As Double NumRows = Range("D65536").End(xlUp).Rows For Iloop = NumRows To 2 Step -1 If IsEmpty(Cells(Iloop, 1)) Then Rows(Iloop).Delete End If Next Iloop End Sub Post back if you need help setting up the macro. -- Ken Hudson "Scott Wagner" wrote: I have a project coming up that one aspect will require a macro to remove certain rows in a spreadsheet that contain detailed product information that really isn't needed. Basically I want to turn a detailed product listing into a summarized product listing. Example of what I will have is below. Will start with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 150A MCB 120Y/208V 02 1 B Panelboard (NQOD TYPE) 200A MCB 120Y/208V 03 1 C Panelboard (I-LINE TYPE) 400A MCB 120Y/208V Want to end up with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 02 1 B Panelboard (NQOD TYPE) 03 1 C Panelboard (I-LINE TYPE) Since the line number appears in a specific cell, lets say B2 then basic logic is that is cell B2 is empty, delete row B. Let me know what you think. Thanks, Scott |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro, delete rows that meet criteria
Hi Scott,
Try: '============= Public Sub Tester() On Error Resume Next Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "Scott Wagner" wrote in message ... I have a project coming up that one aspect will require a macro to remove certain rows in a spreadsheet that contain detailed product information that really isn't needed. Basically I want to turn a detailed product listing into a summarized product listing. Example of what I will have is below. Will start with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 150A MCB 120Y/208V 02 1 B Panelboard (NQOD TYPE) 200A MCB 120Y/208V 03 1 C Panelboard (I-LINE TYPE) 400A MCB 120Y/208V Want to end up with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 02 1 B Panelboard (NQOD TYPE) 03 1 C Panelboard (I-LINE TYPE) Since the line number appears in a specific cell, lets say B2 then basic logic is that is cell B2 is empty, delete row B. Let me know what you think. Thanks, Scott |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro, delete rows that meet criteria
Thanks for the quick response.
I get a run time error 13 (type mismatch) at: NumRows = Range("D65536").End(xlUp).Rows I am not really strong with VBA. "Ken Hudson" wrote: Hi Scott, This should work. Sub DelRows() Dim Iloop As Double Dim NumRows As Double NumRows = Range("D65536").End(xlUp).Rows For Iloop = NumRows To 2 Step -1 If IsEmpty(Cells(Iloop, 1)) Then Rows(Iloop).Delete End If Next Iloop End Sub Post back if you need help setting up the macro. -- Ken Hudson "Scott Wagner" wrote: I have a project coming up that one aspect will require a macro to remove certain rows in a spreadsheet that contain detailed product information that really isn't needed. Basically I want to turn a detailed product listing into a summarized product listing. Example of what I will have is below. Will start with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 150A MCB 120Y/208V 02 1 B Panelboard (NQOD TYPE) 200A MCB 120Y/208V 03 1 C Panelboard (I-LINE TYPE) 400A MCB 120Y/208V Want to end up with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 02 1 B Panelboard (NQOD TYPE) 03 1 C Panelboard (I-LINE TYPE) Since the line number appears in a specific cell, lets say B2 then basic logic is that is cell B2 is empty, delete row B. Let me know what you think. Thanks, Scott |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro, delete rows that meet criteria
PERFECT!!!!!
Thank you so much!!! "Norman Jones" wrote: Hi Scott, Try: '============= Public Sub Tester() On Error Resume Next Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "Scott Wagner" wrote in message ... I have a project coming up that one aspect will require a macro to remove certain rows in a spreadsheet that contain detailed product information that really isn't needed. Basically I want to turn a detailed product listing into a summarized product listing. Example of what I will have is below. Will start with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 150A MCB 120Y/208V 02 1 B Panelboard (NQOD TYPE) 200A MCB 120Y/208V 03 1 C Panelboard (I-LINE TYPE) 400A MCB 120Y/208V Want to end up with this: Line# Qty Marking Description 01 1 A Panelboard (NQOD TYPE) 02 1 B Panelboard (NQOD TYPE) 03 1 C Panelboard (I-LINE TYPE) Since the line number appears in a specific cell, lets say B2 then basic logic is that is cell B2 is empty, delete row B. Let me know what you think. Thanks, Scott |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
| Delete data in cells that don't meet criteria | New Users to Excel | |||
| Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
| how do i delete rows when cells meet certain criteria? | Excel Programming | |||
| Excel 2000 VBA - Delete Rows That Meet Criteria | Excel Programming | |||
Linear Mode
