Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Sep 13, 2019
Solved

VBA: loop not looping

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

 

 

excel
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Sep 13, 2019

    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

     

     

6 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 13, 2019

    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

     

     

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Sep 13, 2019
    I would suggest modeling your code based on the following:
    https://support.microsoft.com/en-us/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook
    • gms4b's avatar
      gms4b
      Brass Contributor
      Sep 13, 2019

      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.

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Sep 13, 2019

        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.

Resources