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

 

[Georgiboy]

Another thing to think about - you can save macros to a personal workbook so they are always available:

Create and save all your macros in a single workbook
or
Copy your macros to a Personal Macro Workbook

 

[Mel Smith]

Thanks Flashbond for your time and trouble.

 

[Mel Smith]

Another thing to think about - you can save macros to a personal workbook so they are always available:

Create and save all your macros in a single workbook
or
Copy your macros to a Personal Macro Workbook

Thanks, Georgiboy, I'll look at that. Very helpful.

Mel

 

[Mel Smith]

Flashbond,

I've copied your code and it does create a copy of the worksheet, which I don't need but it does not delete the rows where cell C4, C5 etc. to cell C63 that display a zero. The zero is the result of: =IF('Sign in to your Microsoft account)

Georgiboy - Personal Macros - thankyou!

Mel

 

[Legacy 143009]

Sorry there is a mistake in the code,
Try like this:

Private Sub Workbook_Open()
         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

 

[Mel Smith]

Thanks for the amendment, Flashbond. Which line(s) can I remove to stop making a copy of the worksheet?

 

[Legacy 143009]

ThisWorkbook.Worksheets(i).Copy Before:=ThisWorkbook.Worksheets(i)

 

[Mel Smith]

Hi Flashbond,

I "commented" that line out and it failed at the line: If ThisWorkbook.Worksheets(i).Cells(ii, 3).Value = 0 Then

Any thoughts?

Mel

 

[Legacy 143009]

Ahh yes.. You also have to remove i = i + 1

 

[Mel Smith]

Sadly, now the macro runs but nothing happens