Hi all,

I need a loop to zero values on sheets in my workbook. It will be best to have a look at the sheet attached, I will explain further.

The sheets vary in length from day to day.
For sheet "Emb" I need the columns "H" and "I" zeroed if grater than 0, starting at row 5 and is fixed at this row, but not the totals.
My problem is however the second "block", "machine 2" that does not always start from row 14 as per this example.

For the rest of the sheets, columns "O" and "T", starting at row 5 and is fixed, and again not zeroing the total.

Many thanks.

Try this macro:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
Select Case ws.Name
Case "EMB"
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
For i = 5 To LR
If ws.Cells(i, "A") <> "" Then
If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
End If
Next i
Case "Prod A" To "Prod D"
For i = 5 To LR
If ws.Cells(i, "A") <> "" Then
If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
End If
Next i
End Select
Next ws

Application.ScreenUpdating = True
End Sub

Is doing the work needed thank you.

But have a look at the newest attachment, the cells marked with blue in "Emb" sheet must not change, "H12:I13", my problem is these cells move up or down daily as rows are inserted or deleted, is this possibal?

Again, many thanks.

You need to remove the 45 empty module sheets in your workbook. Very confusing.

Then try this:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
Select Case ws.Name
Case "EMB"
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
For i = 5 To LR
Select Case ws.Cells(i, "A").Value
Case "", "A" To "Z"
'do nothing
Case Else
If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
End Select
Next i
Case "Prod A" To "Prod D"
For i = 5 To LR
If ws.Cells(i, "A") <> "" Then
If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
End If
Next i
End Select
Next ws

Application.ScreenUpdating = True
End Sub

The emty modules actually contains macros, had to get the file to a size that can be uploaded.

Ah, in that case, next time maybe you can save yourself a lot of erasing and try zipping the file.

Is this sufficiently resolved?

Zip, ah, forgetting the simple things when your mind is in a twist with VBA, next time.

Thank you very very much, the macro works beutifully and saves me plenty time, you rock JB.

Don't know if this is too much, but don't understand some of the VBA word used like "LR" and "Case". Could you walk me through the macro so I can build on my knowledge? If too much don't worry.

Anything on the DIM line is variables that I am defining. I use LR in all my macros to store the "last row" value. Notice a little further down I "solve" the LR with the LR = ws.Range....? That's me using my variable.


Select Case is a VBA equivalent of stacking IF statements in a worsheet formula. If you know you are going be evaluating some piece of data over and over, and when it equals one value then DO THIS, and if it equals this other value then DO THAT, the Select Case is the method to make that easy to read and manage.

First you set the SELECT CASE?? And the?? Is the value you're evaluating. In your macro, it's the worksheet name.

We use Case "emb" as the first "If the worksheet name is emb, then DO THIS". All the code inside the case is executed. The next CASE statement signifies the start of the next "if the worksheet name is ???????, then DO THIS"...

These cases allow you to code specific behaviors based on different values.

Here's a fully commented version of the code:

Sub ZeroValues()
'Declare all the variables I'm going to use
Dim LR As Long
Dim i As Long
Dim ws As Worksheet
'Turn off screen refreshing so code executes faster
Application.ScreenUpdating = False

'Cycle through all the worksheets one at a time
For Each ws In Worksheets
'Look at each sheet name and run code specific to each sheet
'store the worksheet name
Select Case ws.Name
'if the sheet name is "EMB"
Case "EMB"
'Find the Last row of data by looking at column H
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
'Loop through all the rows from row 5 through the last row
For i = 5 To LR
'another select case section
'store the value from column A for this row
Select Case ws.Cells(i, "A").Value
'if cell "A" is blank or has text in it, do nothing
Case "", "A" To "Z"
'do nothing
'for the numeric cells, change all values to zero
Case Else
If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
End Select
'next row on this sheet
Next i
'If the sheet name is Prod A - D
Case "Prod A" To "Prod D"
'Find the Last row of data by looking at column H
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
'Loop through all the rows from row 5 through the last row
For i = 5 To LR
'If there is a value in column A....
If ws.Cells(i, "A") <> "" Then
'...then change values in O and T to zero
If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
End If
'next row on this sheet
Next i
'No more "cases" for sheet names
End Select
'Move on to the next sheet and start the loop above all over again
Next ws

Application.ScreenUpdating = True
End Sub

Here's a slightly tweaked version, I noticed I'd forgotten the LR solution for the second case.

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
Select Case ws.Name
Case "EMB"
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
For i = 5 To LR
Select Case ws.Cells(i, "A").Value
Case "", "A" To "Z"
'do nothing
Case Else
If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
End Select
Next i
Case "Prod A" To "Prod D"
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
For i = 5 To LR
If ws.Cells(i, "A") <> "" Then
If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
End If
Next i
End Select
Next ws

Application.ScreenUpdating = True
End Sub

Thank you very much! What software are you using to construct your macros? I just don't see myself getting all the spaces and functions in the right place by typinh the macro from scratch. Amazing what actually can be done with macros.
Awsome work JB.

I type them manually into the VBEditor. Is there a VBA wizard?

Indenting logic sections is done easily with TAB as I go and it makes the code easy to read as "sections".

I use Option Explicit at the top of all my macros (In fact my computer puts it in automatically when I create a new module, it's a setting somewhere in the VBEditor Tools > Options > Auto Syntax Check.)
Option Explicit is the equivalent of "Spell check" and if I type something Excel doesn't recognize, it tells me, immediately or when I run the Debug > Compile VBAProject command.

It's important to always work with Option Explicit if you declare a lot of your own variables.

Thank you very much, ia teaching me a so many new things, like the "case" statement.

Added two lines of code to delete rows giving a certain criteria, but it is deleting the worksheets copmletly blank:eek: What am I missing here?


Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
Select Case ws.Name
Case "EMB"
LR = ws.Range("H" & Rows.Count).End(xlUp).Row
For i = 5 To LR
Select Case ws.Cells(i, "A").Value
Case "", "A" To "Z"
'do nothing
Case Else
If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
If ws.Cells(i, "F") = ws.Cells(i, "G") Then ws.Rows.Delete
End Select
Next i
Case "Prod A" To "Prod D"
For i = 5 To LR
If ws.Cells(i, "A") <> "" Then
If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
If ws.Cells(i, "U") = 0 & ws.Cells(i, "W") = 0 Then ws.Rows.Delete
End If
Next i
End Select
Next ws

Application.ScreenUpdating = True
End Sub

It's doing what you told it to do.

ws.Rows.Delete
... means "delete all rows on the worksheet".

Maybe you meant this:

ws.Rows(i).Delete

Also, if you're going to delete rows as you run through the data, that will cause all the rows below to shift upward. That makes the row you HAVEN'T evaluated yet move up into the row you're currently on, and then the loop moves down to the next row.

The net effect of this is that each time a row is deleted, the next row of data isn't evaluated, it's essentially "skipped" because of the way it moved on you.

So, if you're going to delete rows, always work from the BOTTOM of the data set upward. This way you've already evaluated all the data below that is moving upward with each deletion.

To reverse the order the data is evaluated change this:

For i = 5 To LR

...change that to

For i = LR to 5 Step -1

No joy. Deletes al sorts of info on sheets.

Feel free to post up a sheet you are running this code on with your version of the macro installed. If you include a DESIRED RESULTS page, too, I can compare what is going on to what you're shooting for.

Here are the sheets.

JB, got it sorted, thank you very much for help on this one.