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 wo...

excel

Macros and VBA

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

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

Forum Discussion

VBA: loop not looping

Resources