Delete certain cells if the cells is zero without delete the remaining rows

Fanny18

New Member
Joined
Mar 7, 2022
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I have a range of data from A1 to Y6000 with multiple worksheets.

But i would like to just delete the table (in cell range A1 to Y2) in multiple worksheets if the value of this range is Zero without affecting other data between A3 to Y6000.

Appreciate if i can have the vba code to delete the cells in A1 to Y2 if the cells at Row 2 is Zero with Row 1 is the header. Thanks.

Eg as per screenshot below : How to just delete G1, G2, I1 and I2 cells and move H1:H2 to G1:G2 and so on without affecting other data below such as without deleting A5 to K15?.

1646672938812.png
 

Attachments

  • 1646672846851.png
    1646672846851.png
    45.9 KB · Views: 5

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try:
VBA Code:
Sub DeleteCells()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long
    For Each ws In Sheets
        For x = 25 To 5 Step -1
            With ws
                If .Cells(2, x) = 0 Then
                    .Cells(1, x).Resize(2).Delete
                End If
            End With
        Next x
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try:
VBA Code:
Sub DeleteCells()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long
    For Each ws In Sheets
        For x = 25 To 5 Step -1
            With ws
                If .Cells(2, x) = 0 Then
                    .Cells(1, x).Resize(2).Delete
                End If
            End With
        Next x
    Next ws
    Application.ScreenUpdating = True
End Sub
Thanks you very much, just would like to check how to let it apply to all sheets in the workbook?
 
Upvote 0
Thanks you very much, just would like to check how to let it apply to all sheets in the workbook?

Besides, if sometimes we also not sure which is the last columns? As there are plenty of worksheets. How to do the coding for this kind of case? Thanks.
 
Upvote 0
Try:
VBA Code:
Sub DeleteCells()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long, lCol As Long
    lCol = Range("IV1").End(xlToLeft).Column
    For Each ws In Sheets
        With ws
            lCol = .Range("IV1").End(xlToLeft).Column
            For x = lCol To 5 Step -1
                If .Cells(2, x) = 0 Then
                    .Cells(1, x).Resize(2).Delete
                End If
            Next x
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Try:
VBA Code:
Sub DeleteCells()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long, lCol As Long
    lCol = Range("IV1").End(xlToLeft).Column
    For Each ws In Sheets
        With ws
            lCol = .Range("IV1").End(xlToLeft).Column
            For x = lCol To 5 Step -1
                If .Cells(2, x) = 0 Then
                    .Cells(1, x).Resize(2).Delete
                End If
            Next x
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub
Thanks for your help. it help much to me. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top