VBA, need to replace values of specific number of cells in a row(not the whole row), if the value of the first cell is 0.

[Anunay]

VBA, need to replace values of specific number of cells in a row(not the whole row), if the value of the first cell is 0. For example if the column A(SR NO.1) has a value 0 in any cell then the value of cells in that particular row up to column D needs to be replaced with a blank. Similarly in the Column E if the value of any cell is 0 then the value of cells in that row up to column H needs to be replaced with blank. This format is going to help with my Dataload implementation. A VBA code to counter this problem would be really helpful.

Book1
	A	B	C	D	E	F	G	H
1	SR NO. 1	DATA	CHARGES	AUX	SR NO.2	DATA	CHARGES	AUX
2	0	RANDOM	0	0	1	SPECIFIC	100	1
3	0	RANDOM	0	0	0	RANDOM	0	0
4	1	SPECIFIC	100	1	2	MORE SPECIFIC	200	2
5	0	RANDOM	0	0	0	RANDOM	0	0
6	1	SPECIFIC	100	1	1	SPECIFIC	100	1
Sheet1

 

[EXCEL MAX]

Hello Anunay, welcome.
You can try with few loops.

Sub ClearZeroRow()
  
    Dim vRng, vLastCell As Range, vC As Long, vR As Long, _
             vN1 As Long, vN2 As Long, vN3 As Long
  
    With ActiveSheet
        Set vLastCell = .Cells.SpecialCells(xlLastCell)
        vRng = Range("A1", vLastCell)
        vC = vLastCell.Column
        vR = vLastCell.Row
        For vN1 = 1 To vC Step 4
            For vN2 = 2 To vR
                If vRng(vN2, vN1) = 0 Then
                    For vN3 = 0 To 3
                         vRng(vN2, vN1 + vN3) = ""
                    Next vN3
                End If
            Next vN2
        Next vN1
        .Range("A1").Resize(vR, vC) = vRng
    End With
  
End Sub

 

[Anunay]

Hello Anunay, welcome.
You can try with few loops.

Sub ClearZeroRow()
 
    Dim vRng, vLastCell As Range, vC As Long, vR As Long, _
             vN1 As Long, vN2 As Long, vN3 As Long
 
    With ActiveSheet
        Set vLastCell = .Cells.SpecialCells(xlLastCell)
        vRng = Range("A1", vLastCell)
        vC = vLastCell.Column
        vR = vLastCell.Row
        For vN1 = 1 To vC Step 4
            For vN2 = 2 To vR
                If vRng(vN2, vN1) = 0 Then
                    For vN3 = 0 To 3
                         vRng(vN2, vN1 + vN3) = ""
                    Next vN3
                End If
            Next vN2
        Next vN1
        .Range("A1").Resize(vR, vC) = vRng
    End With
 
End Sub

Genius

 

[EXCEL MAX]

Thanks for feedback.

 

[Peter_SSs]

You can try with few loops.

.. or with no loops at all.

Sub ClearPartRows()
  With Intersect(Rows("2:" & Range("A" & Rows.Count).End(xlUp).Row), Range("A:A,E:E"))
    .Replace What:=0, Replacement:="#N/A", LookAt:=xlWhole
    On Error Resume Next
    Intersect(.Areas(1).SpecialCells(xlConstants, xlErrors).EntireRow, Columns("A:D")).ClearContents
    Intersect(.Areas(2).SpecialCells(xlConstants, xlErrors).EntireRow, Columns("E:H")).ClearContents
    On Error GoTo 0
  End With
End Sub

 

[Anunay]

Guys Check out this new problem i got i can seem to wrap my head around it, i'll delete this message in 9 hours so it doesn't waste this thread.

Excel formatting problem pivot/unpivot

This is the source format i have which i have tried picoting and unpivoting and even transposing for some reason to make it work in the format i have attached below i have even reached a point where my excel looks almost like the output but then the batch numbers are missing...

www.mrexcel.com