Macro, delete rows that meet criteria

[Scott Wagner]

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

[Ken Hudson]

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

[Norman Jones]

Hi Scott,

Try:

'=============>>
Public Sub Tester()

On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub
'<<=============

---
Regards,
Norman


"Scott Wagner" <[email protected]> wrote in message
news:[email protected]...
>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

[Scott Wagner]

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

[Scott Wagner]

PERFECT!!!!!

Thank you so much!!!




"Norman Jones" wrote:

> Hi Scott,
>
> Try:
>
> '=============>>
> Public Sub Tester()
>
> On Error Resume Next
> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> On Error GoTo 0
>
> End Sub
> '<<=============
>
> ---
> Regards,
> Norman
>
>
> "Scott Wagner" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>