I am trying to make a macro that deletes entire rows if in column 70 it does
not say "Manufacturer". I have created one that I think should work, however
it says error 424 object required and it does not say where. Any suggestions?
This is the macro:

Sub ManufacturingMacro( )

Dim Variable
Dim x ' Delete all non-manufacturing companies
x = 1
Do While Cells(x, 70).Value < ""
x = x + 1
Set Variable = Cells(x, 1)
If Variable.Value < "Manufacturer" Then
EntireRow.Delete
End If
Loop

End Sub

It wants to know where the row to delete is.

Variable.EntireRow.Delete

" wrote:

I am trying to make a macro that deletes entire rows if in column 70 it does
not say "Manufacturer". I have created one that I think should work, however
it says error 424 object required and it does not say where. Any suggestions?
This is the macro:

Sub ManufacturingMacro( )

Dim Variable
Dim x ' Delete all non-manufacturing companies
x = 1
Do While Cells(x, 70).Value < ""
x = x + 1
Set Variable = Cells(x, 1)
If Variable.Value < "Manufacturer" Then
EntireRow.Delete
End If
Loop

End Sub

Try the following code:

Sub AAA()
Dim LastRow As Long
Dim RowNdx As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(.Cells(RowNdx, 70).Text, _
"manufacturer", vbTextCompare) < 0 Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With
End Sub

This goes from the last non-blank cell in column A to row 1, testing
column 70, deleting the row if col 70 < "manufacturer".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 16 Feb 2009 12:58:01 -0800,
m wrote:

I am trying to make a macro that deletes entire rows if in column 70 it does
not say "Manufacturer". I have created one that I think should work, however
it says error 424 object required and it does not say where. Any suggestions?
This is the macro:

Sub ManufacturingMacro( )

Dim Variable
Dim x ' Delete all non-manufacturing companies
x = 1
Do While Cells(x, 70).Value < ""
x = x + 1
Set Variable = Cells(x, 1)
If Variable.Value < "Manufacturer" Then
EntireRow.Delete
End If
Loop

End Sub

Public Sub DelManufact()
Dim intNum, intRows As Integer
Dim strMan, strTest As String
Dim a
Application.ScreenUpdating = False
strMan = ""
strTest = ""
intNum = 0
intRows = 0

Range("A1").Select
Set a = Selection

strMan = UCase("manufacture")

Range(a, a.SpecialCells(xlLastCell)).Select
intRows = Selection.Rows.Count
For intNum = 0 To intRows

'use .Text in case the word is a formula type
strTest = UCase(ActiveCell.Offset(0, 0).Text)

If strMan = strTest Then
Rows(intNum).EntireRow.Delete 'shift:=xlUp

'If you delete a row you have to account for it in the toal rows already
determined.
intRows = intRows - 1
ActiveCell.Offset(0, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Next
End Sub

" wrote:

I am trying to make a macro that deletes entire rows if in column 70 it does
not say "Manufacturer". I have created one that I think should work, however
it says error 424 object required and it does not say where. Any suggestions?
This is the macro:

Sub ManufacturingMacro( )

Dim Variable
Dim x ' Delete all non-manufacturing companies
x = 1
Do While Cells(x, 70).Value < ""
x = x + 1
Set Variable = Cells(x, 1)
If Variable.Value < "Manufacturer" Then
EntireRow.Delete
End If
Loop

End Sub