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
Bookmarks