SOLVED

VBA: loop not looping

Brass Contributor

I am trying to have VBA go through each sheet in a workbook, look for the phrase "Sample Name", and delete all of the rows above it. It will find the phrase and delete the rows above for the first worksheet, but will not loop and go on to the next worksheet. Any fixes? 

Thanks,

Greg

 

 

Sub LoopforSub()

Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
        
        'delete rows above phrase "Sample Name"
        Dim MyRange As Range
        Set MyRange = ActiveSheet.Range("A:A")
        MyRange.Find("Sample Name", LookIn:=xlValues).Select
        ActiveCell.Offset(-1, 0).Select
        Range(ActiveCell.Row & ":" & 1).Rows.Delete
    
Next Ws
End Sub

 

 

6 Replies

@PReagan 

 

yeah, ugh. I've tried both of these and neither seems to work. It just never moves on to the second sheet. It just starts applying the code to the same sheet it just did.

 

I have the workbook attached.

best response confirmed by gms4b (Brass Contributor)
Solution

@gms4b 

Here is the proper way to do that...

 

Sub LoopforSub()

Dim Ws      As Worksheet
Dim MyRange As Range
Dim r       As Long

Application.ScreenUpdating = False

For Each Ws In ActiveWorkbook.Worksheets
    'Setting MyRange on each worksheet
    Set MyRange = Ws.Range("A:A").Find("Sample Name", LookIn:=xlValues)
    
    'If Sample Name is found in column A, MyRange will be properly set
    If Not MyRange Is Nothing Then
        r = MyRange.Row
        'If Sample Name is in A1 that means the rows on that Sheet has already been deleted
        'And if you run your existing code, it will produce an error because there is no Row#0
        If r <> 1 Then
            r = r - 1
            'Delete Rows on the Sheet in the loop
            Ws.Rows(r & ":" & 1).Delete
        End If
    End If
    Set MyRange = Nothing
Next Ws
Application.ScreenUpdating = True
End Sub

 

 

@gms4b 

The code I posted above works perfectly on your sample file.

Please test it and let me know if that works fine on your end too.

 

The problem with your earlier code was, you were setting MyRange variable on the ActiveSheet and you never activated another sheet inside the For Loop.

@Subodh_Tiwari_sktneer 

 

Perfect! Thank you for the help. It works great now.

 

Greg

 

 

You're welcome Greg! Glad it worked as desired.

 

Regards,

Subodh

 

1 best response

Accepted Solutions
best response confirmed by gms4b (Brass Contributor)
Solution

@gms4b 

Here is the proper way to do that...

 

Sub LoopforSub()

Dim Ws      As Worksheet
Dim MyRange As Range
Dim r       As Long

Application.ScreenUpdating = False

For Each Ws In ActiveWorkbook.Worksheets
    'Setting MyRange on each worksheet
    Set MyRange = Ws.Range("A:A").Find("Sample Name", LookIn:=xlValues)
    
    'If Sample Name is found in column A, MyRange will be properly set
    If Not MyRange Is Nothing Then
        r = MyRange.Row
        'If Sample Name is in A1 that means the rows on that Sheet has already been deleted
        'And if you run your existing code, it will produce an error because there is no Row#0
        If r <> 1 Then
            r = r - 1
            'Delete Rows on the Sheet in the loop
            Ws.Rows(r & ":" & 1).Delete
        End If
    End If
    Set MyRange = Nothing
Next Ws
Application.ScreenUpdating = True
End Sub

 

 

View solution in original post