Loop to delete rows doesn't work on coworkers comp

[xljunkie]

Fairly new to VBA here. I found some code online to delete rows with a particular word/value in column A. It works fine on my computer but won't work on a coworker's. Any idea why?

We're both on Excel 2003. I've tried taking out the portion of the code that adjusts the view and calc mode but the problem persists.

Dim stringToFind As String
Windows("Macro.xls").Activate
    stringToFind = Range("A22")
'Cell A22 above is a particular word.
 
' Procedure below activates a 3rd file whose filename changes from month to month
For Each WkBook In Workbooks
    If WkBook.Name <> "Query.xls" And WkBook.Name <> "Macro.xls" Then
        WkBook.Activate
        Exit For
    End If
Next WkBook
 
'Code below deletes all rows with the stringToFind value
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'We use the ActiveSheet
    With ActiveSheet
        'We select the sheet so we can change the window view
        .Select
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value = stringToFind Then .EntireRow.Delete
                    'This will delete each row with the Value stringToFind
                    'in Column A, case sensitive.
                End If
            End With
        Next Lrow
    End With
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

 

[jreedich]

Do you get any error messages on your co-workers computer???

 

[xljunkie]

No error messages. This piece of the macro is only a part of the whole thing so I don't know if there are other areas of the code that could be causing this. I don't think there are other areas that affect this.

The macro performs through completion except the rows I want deleted using this loop don't get deleted on his computer. I checked on another person's computer and it also doesn't remove these rows.

 

[Roli001]

I would recommend that you put the value 'stringToFind' on the last row in column A and then run the code line by line (F8) to see if the code is capturing the cell value correctly.

 

[xljunkie]

Thanks for the tip. I'll try that out tomorrow.

But why would the location of the cell matter?

 

[Roli001]

Thanks for the tip. I'll try that out tomorrow.

But why would the location of the cell matter?

Because you are looping from the last cell up so this would be the first cell to be tested....

 

[pcc]

Maybe case sensitivity.
Put

Option Compare Text

at the top of the module that contains your code, and try again

 

[xljunkie]

Neither of the methods worked - moving the stringToFind value to the last row of the workbook that contains the macro and Option Compare Text.

I haven't had the chance to go through the code line by line. It's a coworker's computer so I'll need to find a time when he's not working on it.

 

[jeffreybrown]

I did a quick test and it seems a filter would be a better use of code IMO.

Update references to workbooks and or worksheets.

Sub DelRows2()
    Dim stringToFind As String
    Windows("Macro.xls").Activate
    stringToFind = Range("A22")

    Application.ScreenUpdating = False
    Windows("Book1.xlsm").Activate
    
    With Sheets("Sheet6").Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:=stringToFind
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[jeffreybrown]

I made a slight update. I believe this version is qualified a little better, plus we can drop the activate part (of course the workbook should be open at the time).

Sub DelRows2()
    Dim stringToFind As String
    Dim srcWB As Workbook: Set srcWB = Workbooks("Macro.xls")
    Dim destWB As Workbook: Set destWB = Workbooks("Book1.xlsm")
    
    Application.ScreenUpdating = False
    
    With srcWB.Sheets("Sheet1")
        stringToFind = .Range("A22")
    End With
    
    With destWB.Sheets("Sheet6").Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:=stringToFind
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
    Set srcWB = Nothing
    Set destWB = Nothing
End Sub