Macro to delete rows dependent on cell value

[Mel Smith]

I have a several (almost identical) spreadsheets that I regularly need to delete rows based upon the value displayed in each cell within the range C4:C63. The cells in this row get their value via a link from another workbook. What I am trying to achieve is: if cell C4 shows 0 then delete row and so on down to and including row 63. I would like, if possible, for this macro to be available every time I open Excel so that I can call it whenever I need it rather than re-writing it every day.

Thanks for your help.

Mel

 

[Legacy 143009]

Do you need this macro in all spreadsheets?
I mean all!

 

[Mel Smith]

Hi Flashbond,

Yes. What I have to do is copy the original sheet, then delete rows in the newly copied sheet but leave the original unchanged.

does that make sense?

Mel

 

[Legacy 143009]

Private Sub Workbook_Open()
         Dim WS_Count As Integer
         WS_Count = ActiveWorkbook.Worksheets.Count
         For i = 1 To WS_Count
               ActiveWorkbook.Worksheets(i).Copy
               For ii = 63 To 4 Step -1
                    If ActiveWorkbook.Worksheets(i).Cells(i, 3).Value = 0 Then
                          ActiveWorkbook.Worksheets(i).Cells(i, 3).EntireRow.Delete
                    End If
               Next
         Next
End Sub

 

[Mel Smith]

Thanks, Flashbond. Two questions: where should I store this code and will it affect any other workbook/sheets that i may have open?

Mel

 

[Legacy 143009]

Thanks, Flashbond. Two questions: where should I store this code and will it affect any other workbook/sheets that i may have open?

Mel

Right click to any sheet and select macro. On the left pane double click to Workbook. Paste the code there.
Yes, this may affect any active workbook. To make is specific for that workbook explicitly, then try this code:

Private Sub Workbook_Open()
         Dim WS_Count As Integer
         WS_Count = ThisWorkbook.Worksheets.Count
         For i = 1 To WS_Count
               ThisWorkbook.Worksheets(i).Copy
               For ii = 63 To 4 Step -1
                    If ThisWorkbook.Worksheets(i).Cells(i, 3).Value = 0 Then
                          ThisWorkbook.Worksheets(i).Cells(i, 3).EntireRow.Delete
                    End If
               Next
         Next
End Sub

 

[Mel Smith]

When I right-click, I get a menu appear but nothing referring to Macro....

 

[Legacy 143009]

When I right-click, I get a menu appear but nothing referring to Macro....

It says "View Code"

 

[Mel Smith]

Aah, you meant on the tab - found it!

Will this macro always be visible when I copy a new sheet tomorrow etc?

 

[Legacy 143009]

Yes but you have to save the file as xlsm file only for once. xlsx files do not support to save macro.
Another problem will be, this code duplicates worksheets per your request. So each time you open workbook, you are going to have dublicates of previous duplicates. Also it will loop through that previosly created sheets and delete rows.
You have to reconsider your workflow.