Delete Every Zero Value in Same Column In Every Worksheet

[stuartgb100]

Hi,

In column "G", Rows 7 - 2,000 there are mainly numbers, but some text.
Many cells show as 0 (ie zero), and I would like to delete these.
I have over a hundred sheets to deal with.
The workbook I have been given is over 6 Mb.

How do I loop through each worksheet and also loop through each cell in the range "G7:G200" please ?

 

[Joe4]

Are you trying to delete the whole row, or just clear the 0 entry out of that cell?

If the later, you can use Find/Replace on column G of all sheets and replace the 0's with nothing.
If you turn on the Macro Recorder while you perform this step manually on one sheet, it will give you the VBA code you need to do that.
If you need to see what VBA code that loops through all sheets of a workbook looks like, have a look at this post I just made here: https://www.mrexcel.com/forum/excel...d-specific-text-end-all-tabs.html#post4937304

 

[brkkrkmz112]

To be more clear, do you want to move shift cells ?

 

[Johnny C]

Are there any sheets that shouldn't be done?
If so, whee I've got the If sht.Name <> "Control" etc put your sheet names you don't want done in there. If you've no sheets remove the IF statement and End If at the bottom

Add a module to your workbook or personal.xlsb
Add this

Then run it
Turning Calculation mode to manual speeds it up.
Also, unmaximise your screen size and make the Excel window quite small, deleting rows forces Excel to refresh toe view and the smaller it is the faster it is.

You use Step -1 and start at the bottom because if you start at the top, when you delete a row, the rows below shift up and you would need to deal with the row that had shifted up before carrying on down. Starting at the bottom avoids this problem

Sub DelZeros
Application.Calculation = xlCalculationManual
For each sht in ActiveWorkbook.Sheets
   if sht.Name <> "Control" And sht.Name <> "Summary" Then
       For RowCounter = 2000 to 7 Step -1
           If sht.Cells(RowCounter,7).Value = 0 Then sht.Range("A"&RowCounter).EntireRow.Delete
       Next RowCounter
    End If
Next sht
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Joe4]

If you are actually wanting to delete the rows, there are ways of doing it without having to loop through every single row on every single sheet (which, while it does work, can be pretty slow, especially if you have a lot of sheets and rows to get through).
Take a look at the VBA approach shown here: https://www.extendoffice.com/documents/excel/1747-excel-delete-row-if-zero.html

 

[stuartgb100]

Many thanks to all for their reply.

Plenty here for me to work with.

Sorry I wasn't clear - just trying to delete the value (clear the cell) if it's a zero.

Thanks again.

 

[Joe4]

Try this:

Sub MyMacro()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In Worksheets
        ws.Range("G7:G2000").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

 

[Johnny C]

Just reread your post, if you don't want to delete the entire row use this

Sub DelZeros
Application.Calculation = xlCalculationManual
For each sht in ActiveWorkbook.Sheets
   if sht.Name <> "Control" And sht.Name <> "Summary" Then
       For RowCounter = 2000 to 7 Step -1
           If sht.Cells(RowCounter,7).Value = 0 Then sht.Range("G"&RowCounter).Delete Shift:=xlUp
       Next RowCounter
    End If
Next sht
Application.Calculation = xlCalculationAutomatic
End Sub