title: Most efficient way to loop through cells Column-wise
body: <p>I need to loop through each cell of a sheet, column-wise. I figured, converting a range to array is probably the most sensible option since I need to loop through 5000 rows across 68 columns.</p>
  
  <p>I need to replace the Zeroes for over 5000 rows (in different positions) with a set value. The image below is what my table looks like, .the Zeroes would need to be replaced with the average values of the corresponding row</p>
  
  <p><a href="https://i.stack.imgur.com/FX3go.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FX3go.png" alt="enter image description here"></a></p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub searchreplace()    
      Dim wb As Workbook
      Set wb = ThisWorkbook
  
      Dim i As Long
      Dim j As Long
      Dim lr As Long
      Dim col As Long
      Dim rng() As Variant
      Dim ws As Worksheet
      Dim arr As Variant
  
      Set ws = wb.Worksheets(1)
  
      'lr = last row with data
      Set ws = ActiveSheet
  
      With ActiveSheet
          lr = Range("C" &amp; Rows.Count).End(xlUp).Row
  
          ' last column number MO66
          col = Range("BP1").Column
  
          'clumn with avg values
          colavg = Range("BQ1").Column
  
          'Debug.Print colavg
  
          'Define range
          'cells(row,col)!    
  
          'my range stored as an array
          rng = Range(Cells(3, 3), Cells(3, col + 6)). Value
  
          'looping through each value of the array
          For i = 1 To 70 '70 columns
              If rng(1, i) = 0 Then
                  rng(1, i) = 5.34
  
              End If  
          Next i    
      End With
  End Sub
  </code></pre>