I have a woksheet that has has 61 Columns.

Column A has a date.
Column B through Column G has 6 different product information for the same entity.
Column H through Column M has 6 differnet product information for the same entity, etc.

I want to find the first non-zero in column B, then in all previous rows above the non-zero I want to have the zeros replaced with an empty cell for the 6 columns of the same entity.

Currently I have the code below but it takes a long time to execute. Is there a way to do this wtih an array and how?

Any help is appreciated, I am definately over my head with my limited knowledge.


Dim lngLastRow As Long, lngLastColumn As Long
Dim r As Integer, x As Integer, b As Integer, c As Integer, j As Variant
Dim rngEntity As Range, Begcell, EndCell


lngLastRow = Range("a1048576").End(xlUp).Row
lngLastColumn = Range("xfd1").End(xlToLeft).Column    
For x = 2 To ((lngLastColumn - 1))
                
                For b = 2 To lngLastRow
                If Cells(b, x).Value = 0 Then
                Else
                c = Cells(b, x).Column
                r = Cells(b, x).Row
                Exit For
                End If
                Next b

               Set Begcell = Cells(2, c)
               Set EndCell = Cells(r, c + 5)
               Set rngEntity = Range(Begcell, EndCell)
   
              For Each j In rngEntity
                If IsNumeric(j.Value) And j.Value = 0 Then
                j.Value = vbNullString
                End If
                Next
                               
                Application.GoTo Reference:="R2C" & c + 5
              x = c + 5
 Next ' for columns