Sep 13 2019 07:39 AM - edited Sep 13 2019 07:40 AM
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
Sep 13 2019 08:09 AM
Sep 13 2019 08:24 AM
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.
Sep 13 2019 08:29 AM
SolutionHere 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
Sep 13 2019 08:33 AM
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.
Sep 13 2019 08:57 AM
Sep 13 2019 08:59 AM
You're welcome Greg! Glad it worked as desired.
Regards,
Subodh
Sep 13 2019 08:29 AM
SolutionHere 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