Refine/amend code

[Mel Smith]

One of our community gave me the following code and I have been trying, without success to refine it and make it work. All I'm trying to achieve is to delete any row between (and including) row 4 to 63 when the value in cell c4:c63 is zero. I do not want nor need a copy of the worksheet. I have the macro stored in my personal .xlsb. I have tried 'commenting out various lines, all to no effect.

Sub Delete_Rows()
'
For i = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(i).Copy Before:=ThisWorkbook.Worksheets(i)
i = i + 1
For ii = 63 To 4 Step -1
If ThisWorkbook.Worksheets(i).Cells(ii, 3).Value = 0 Then
ThisWorkbook.Worksheets(i).Cells(ii, 3).EntireRow.Delete
End If
Next
Next
End Sub

Thanks for your help.

Mel

 

[MARK858]

You need to change everywhere it says

ThisWorkbook

to

ActiveWorkbook

if you are storing the macro in your personal workbook

 

[Mel Smith]

Many thanks, Mark. How may I stop a copy of the worksheet being made?

Mel

 

[Mel Smith]

Thanks for your observation, Mark. I have now achieved the solution I wanted with the following code. I have "commented out" lines two and three:

For i = 1 To ActiveWorkbook.Worksheets.Count
               'ActiveWorkbook.Worksheets(i).Copy Before:=ActiveWorkbook.Worksheets(i)
               'i = i + 1
               For ii = 63 To 4 Step -1
                    If ActiveWorkbook.Worksheets(i).Cells(ii, 3).Value = 0 Then
                          ActiveWorkbook.Worksheets(i).Cells(ii, 3).EntireRow.Delete
                    End If
               Next
         Next
End Sub

Mel

 

[MARK858]

Happy you have it sorted

 

[Peter_SSs]

@Mel Smith
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time in post #4.

 

[Mel Smith]

Hello Peter,

When can I see the instructions to post code in the correct manner?

Mel

 

[Peter_SSs]

When can I see the instructions to post code in the correct manner?

Just as I said in my previous post

My signature block below has more details.

In my signature block at the bottom of each of my posts is the following ..