Delete a row from excel sheet 2

[junkie8]

Hello,

I am trying to delete the rows have a some commonality with the column in another sheet. But the code is not working

Sub RemovePlants()

Dim plant As Range
Dim plant2 As Range
Dim plantStr As String
Dim plantStr2 As String

Workbooks("plantopdata.xlsm").Sheets("RemovePlants").Activate
For Each plant2 In Range("A1:A2")
plantStr2 = plant2.Value
Workbooks("plantopdata.xlsm").Sheets("plantopdata").Activate
For Each plant In ActiveSheet.Range("C300:C320")
plantStr = plant.Value
If plantStr = plantStr2 Then
ActiveSheet.Rows(plant.Row).Delete
End If
Next plant
Workbooks("plantopdata.xlsm").Sheets("RemovePlants").Activate
Next plant2

End Sub

 

[macropod]

Hi junkie8,

What isn't working, and what are the values you're working with?

FWIW, you code could also be made more efficient:

Sub RemovePlants()
Dim plant1 As Range
Dim plant2 As Range
Dim StrPlant As String
Dim oBook As Workbook
Set oBook = Workbooks("Book1")
With oBook.Sheets("RemovePlants")
  For Each plant2 In .Range("A1:A2")
    StrPlant = plant2.Value
    With oBook.Sheets("plantopdata")
      For Each plant1 In .Range("C300:C320")
        If plant1.Value = StrPlant Then .Rows(plant1.Row).Delete
      Next plant1
    End With
  Next plant2
End With
Set oBook = Nothing
End Sub

Cheers
[MS MVP - Word]

 

[macropod]

Oh, and change 'Book1' in the code I posted to 'plantopdata.xlsm'.


Cheers
[MS MVP - Word]

 

[SkipVought]

Hi,

FYI, this code structure will NOT work when DELETING, as you destroy the loop reference...

dim r as range
for each r in SomeRange
  [red][b]r.entirerow.delete[/b][/red]
next
Rather loop from the BOTTOM UP...
[code]
dim r as long, r1 as long, r2 as long
with SomeRange
  r1 = .row
  r2 = .rows.count + r1 - 1
end with
for r = r2 to r1 step -1
  [red][b]cells(r.1).entirerow.delete[/b][/red]
next
AND my must be aware that Insert or Delete can mess up formula references.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[junkie8]

hi skipvought,

What does r.1 in

for r = r2 to r1 step -1
cells(r.1).entirerow.delete
next

mean?

 

[junkie8]

Okay, thanks to both skipvought and macropod, I have a working version of the code.

Sub RemovePlants()
Dim plant1 As Range
Dim plant2 As Range
Dim StrPlant As String
Dim oBook As Workbook
Dim r, r1, r2 As Long

Set oBook = Workbooks("plantopdata.xlsm")
With oBook.Sheets("RemovePlants")
For Each plant2 In .Range("A1:A52")
StrPlant = plant2.Value
With oBook.Sheets("plantOpSheet")
With .Range("C2:C41034")
r1 = .Row
r2 = .Rows.Count + r1 - 1
For r = r2 To r1 Step -1
If StrPlant = .Cells(r, 1) Then
.Cells(r, 1).EntireRow.Delete
End If
Next
End With
End With
Next plant2
End With
Set oBook = Nothing
End Sub

 

[SkipVought]

What does r.1 ...

It was a TYPO, sorry, should have been a comma...

dim r as long, r1 as long, r2 as long
with SomeRange
  r1 = .row                  'starting row of SomeRange
  r2 = .rows.count + r1 - 1  'ending row of SomeRange
end with
for r = r2 to r1 step -1
  cells(r,1).entirerow.delete
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}