Frustrated with VB Code

[Desu Nota from Columbus]

I've been failing all day exhausting all possible reasons that this code won't do as I ask. Please help me!

In column BB, there can be the following things:

shift
stub_shift
job
stub_job

I want to delete all rows that do not contain "shift". Easy enough right? <-------- WRONG

Dim lRow As Long
Dim iRow As Long
 
    With Sheet1
        lRow = .Range("BB" & .Rows.Count).End(xlUp).Row
        For iRow = lRow To 1 Step -1
            Select Case .Cells(iRow, "BB").Value
                Case "stub_shift", "job", "stub_job"
                    .Rows(iRow).Delete
            End Select
        Next iRow
    End With

DID NOT WORK

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->[FONT=&quot]

Dim lRow As Long
Dim iRow As Long 
 With Sheet1
        LR = .Range("U" & .Rows.Count).End(xlUp).Row
        For r = 1 To LR
            Select Case .Range("U" & r).Value
                Case Is = "Stub_Shift"
                    EntireRow.Delete Shift:=xlUp
                Case Is = "Job"
                    EntireRow.Delete Shift:=xlUp
                Case Is = "Stub_Job"
                    EntireRow.Delete Shift:=xlUp
            End Select
        Next r
    End With

DID NOT WORK!!!!!!!!!!

[/FONT]<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Thought maybe the fact the criteria was based on a string was causing nothing to happen, so I tried assigning a value to "shift" and deleting all values not equal to "shift"

Sub Turd()
    Dim LR As Long
    Dim r As Long
    
    Range("BC:BC").FormulaR1C1 = "=IF(RC[-1]="""","""",IF(RC[-1]=""shift"",2,0))"
    
    With Sheet1
        LR = .Range("BC" & .Rows.Count).End(xlUp).Row
        For r = 2 To LR
            If Range("BC" & r).Value <> 2 Then
                Range("BC" & r).Select
                Selection.EntireRow.Delete shift:=xlUp
            End If
        Next r
    End With

[FONT=&quot]DID NOT WORK[/FONT]

 

[Jonmo1]

The first one you posted works just fine for me...

Dim lRow As Long
Dim iRow As Long
 
    With Sheet1
        lRow = .Range("BB" & .Rows.Count).End(xlUp).Row
        For iRow = lRow To 1 Step -1
            Select Case .Cells(iRow, "BB").Value
                Case "stub_shift", "job", "stub_job"
                    .Rows(iRow).Delete
            End Select
        Next iRow
    End With

Can you describe "DID NOT WORK" ??

Did it give an error? What error?
Did it not do anything at all?
Did it do something, but not the right thing?
What DID it do?


Perhaps it's referring to the wrong sheet...

This line

With Sheet1

Perhaps it should be

With Sheets("Sheet1")

 

[sous2817]

Does this have anything to do with you post here:

http://www.mrexcel.com/forum/showthread.php?t=552809

One thing to check, does the column you want even have the data your looking for. In a helper column, try:

=IF(BB1="shift", TRUE,FALSE)

and copy down.

Verify that you've got "shift" and not " shift" or "shift " or " shift " in the cells you're looking to delete.

Also, in your other post, you're deleting a bunch of columns before removing the "shift" rows. Verify that you're actually looping through the correct column.

Hopefully one of those two ideas will get you going in the right direction.

If all else fails, post your workbook on a filesharing site and we can take a look at the data as it sits in your workbook.

 

[Desu Nota from Columbus]

yes, the With Sheet1 needed to be With Sheets("Sheet1")

It works now.


Thank you very much!


Edit---

Why does the Sheet1 not work? Any ideas?

 

[Jon von der Heyden]

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=552809

Per the forum rules duplicate question threads will be locked or deleted. In future please stick to one thread.