SOLVED
Home

VBA: loop not looping

%3CLINGO-SUB%20id%3D%22lingo-sub-854502%22%20slang%3D%22en-US%22%3EVBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854502%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20have%20VBA%20go%20through%20each%20sheet%20in%20a%20workbook%2C%20look%20for%20the%20phrase%20%22Sample%20Name%22%2C%20and%20delete%20all%20of%20the%20rows%20above%20it.%20It%20will%20find%20the%20phrase%20and%20delete%20the%20rows%20above%20for%20the%20first%20worksheet%2C%20but%20will%20not%20loop%20and%20go%20on%20to%20the%20next%20worksheet.%20Any%20fixes%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20LoopforSub()%0A%0ADim%20Ws%20As%20Worksheet%0AFor%20Each%20Ws%20In%20ActiveWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20'delete%20rows%20above%20phrase%20%22Sample%20Name%22%0A%20%20%20%20%20%20%20%20Dim%20MyRange%20As%20Range%0A%20%20%20%20%20%20%20%20Set%20MyRange%20%3D%20ActiveSheet.Range(%22A%3AA%22)%0A%20%20%20%20%20%20%20%20MyRange.Find(%22Sample%20Name%22%2C%20LookIn%3A%3DxlValues).Select%0A%20%20%20%20%20%20%20%20ActiveCell.Offset(-1%2C%200).Select%0A%20%20%20%20%20%20%20%20Range(ActiveCell.Row%20%26amp%3B%20%22%3A%22%20%26amp%3B%201).Rows.Delete%0A%20%20%20%20%0ANext%20Ws%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-854502%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854558%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854558%22%20slang%3D%22en-US%22%3EI%20would%20suggest%20modeling%20your%20code%20based%20on%20the%20following%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F142126%2Fmacro-to-loop-through-all-worksheets-in-a-workbook%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F142126%2Fmacro-to-loop-through-all-worksheets-in-a-workbook%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854575%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyeah%2C%20ugh.%20I've%20tried%20both%20of%20these%20and%20neither%20seems%20to%20work.%20It%20just%20never%20moves%20on%20to%20the%20second%20sheet.%20It%20just%20starts%20applying%20the%20code%20to%20the%20same%20sheet%20it%20just%20did.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20workbook%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854577%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20proper%20way%20to%20do%20that...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20LoopforSub()%0A%0ADim%20Ws%20%20%20%20%20%20As%20Worksheet%0ADim%20MyRange%20As%20Range%0ADim%20r%20%20%20%20%20%20%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0AFor%20Each%20Ws%20In%20ActiveWorkbook.Worksheets%0A%20%20%20%20'Setting%20MyRange%20on%20each%20worksheet%0A%20%20%20%20Set%20MyRange%20%3D%20Ws.Range(%22A%3AA%22).Find(%22Sample%20Name%22%2C%20LookIn%3A%3DxlValues)%0A%20%20%20%20%0A%20%20%20%20'If%20Sample%20Name%20is%20found%20in%20column%20A%2C%20MyRange%20will%20be%20properly%20set%0A%20%20%20%20If%20Not%20MyRange%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20r%20%3D%20MyRange.Row%0A%20%20%20%20%20%20%20%20'If%20Sample%20Name%20is%20in%20A1%20that%20means%20the%20rows%20on%20that%20Sheet%20has%20already%20been%20deleted%0A%20%20%20%20%20%20%20%20'And%20if%20you%20run%20your%20existing%20code%2C%20it%20will%20produce%20an%20error%20because%20there%20is%20no%20Row%230%0A%20%20%20%20%20%20%20%20If%20r%20%26lt%3B%26gt%3B%201%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20r%20-%201%0A%20%20%20%20%20%20%20%20%20%20%20%20'Delete%20Rows%20on%20the%20Sheet%20in%20the%20loop%0A%20%20%20%20%20%20%20%20%20%20%20%20Ws.Rows(r%20%26amp%3B%20%22%3A%22%20%26amp%3B%201).Delete%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0A%20%20%20%20Set%20MyRange%20%3D%20Nothing%0ANext%20Ws%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854589%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20I%20posted%20above%20works%20perfectly%20on%20your%20sample%20file.%3C%2FP%3E%3CP%3EPlease%20test%20it%20and%20let%20me%20know%20if%20that%20works%20fine%20on%20your%20end%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20with%20your%20earlier%20code%20was%2C%20you%20were%20setting%20MyRange%20variable%20on%20the%20ActiveSheet%20and%20you%20never%20activated%20another%20sheet%20inside%20the%20For%20Loop.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854664%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerfect!%20Thank%20you%20for%20the%20help.%20It%20works%20great%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854665%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20loop%20not%20looping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854665%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20Greg!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESubodh%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
gms4b
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
Highlighted

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

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

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies