VBA Loop Through Range

[backfromthebush]

Hi all,

I have a table with data in range B2:DY32. I need a code that will loop through all these cells and if the cell is empty then it will populate it with the value from the cell to the immediate left e.g. if cell U4 is empty, then its value will be the same as T4.

Any suggestions??

 

[Smitty]

What if T4's empty?

 

[Trebor76]

Hi backfromthebush,

There's probably more efficient ways, but as it's a small data range try this:

Sub Macro1()

    Dim rngCell As Range, _
        rngMyData As Range
        
    Set rngMyData = Range("B2:DY32")
    
    Application.ScreenUpdating = False
    
    For Each rngCell In rngMyData
    
        With rngCell
        
            If Len(.Value) = 0 Then
            
                .Value = .Offset(0, -1).Value
                
            End If
            
        End With
        
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Note it will still return nothing if there's indeed nothing in the cell to the immediate left of the active cell.

Regards,

Robert

 

[backfromthebush]

Hey guys, thats exactly what i was after! Works a treat!