Do Until replay tree times

Question

To determine whether a cell is a number or not before deleting it, I'm using this code, however I have three columns to check. However, Do Until merely executes the loop once before ceasing all execution and changing the col to 5 or 8 as it is in the for loop.

Could you please explain to me what I'm doing incorrectly in this code?

Another issue I've run into is that when a cell is empty, VBA automatically fills it with the number 0. Is there a method to prevent this from happening?

Sub copy()

    Dim Row As Long
    Dim Col As Long

    Row = 1

    For Col = 2 To 8 Step 3

      Do Until Cells(Row, 1).Value = ""

        If IsNumeric(Cells(Row, Col)) = False Then
            Cells(Row, Col).Clear          
        Else

            Cells(Row, Col).Select

            If Cells(Row, Col).Value = 0 Then

               Cells(Row, Col).Value = (Cells(Row, Col).Value) * 1
               Cells(Row, Col).NumberFormat = "$ #,##0.00"

            Else

                Cells(Row, Col).Value = CDec((Cells(Row, Col).Value))
                Cells(Row, Col).NumberFormat = "$ #,##0.00"
            End If
  

        End If
        Row = Row + 1

    Loop

  Next

End Sub

Answer 1

Clean Data: Apply Consistent Formatting in Columns

Option Explicit

Sub UpdateCurrency()

    ' Define constants.
    Const FIRST_ROW As Long = 2 ' adjust: you have headers, right?
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' Hard to believe that you know the column numbers but not the worksheet name.
    
    ' Calculate the last row, the row of the bottom-most non-empty cell
    ' in the worksheet.
    Dim lCell As Range
    Set lCell = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' no data
    Dim LastRow As Long: LastRow = lCell.Row

    Dim crg As Range, cell As Range, cValue, Col As Long
      
    For Col = 2 To 8 Step 3 ' to not introduce further complications
        
        ' Reference the single-column range from the first to the last row.
        Set crg = ws.Range(ws.Cells(FIRST_ROW, Col), ws.Cells(LastRow, Col))
        
        ' Clear the undesired values (all except empty and numeric values).
        For Each cell In crg.Cells
            ' Write the cell value to a variant variable.
            cValue = cell.Value
            ' Check if the value is not numeric.
            If Not IsNumeric(cValue) Then cell.ClearContents
        Next cell
        
        ' Apply the formatting to the whole column range so it takes effect
        ' if you decide to add numbers to the empty cells.
        crg.NumberFormat = "$ #,##0.00" ' "\$ #,##0.00" if $ is not native
        ' Copy the values to memory, and copy them back to the range
        ' for the formatting to affect the remaining numerics
        ' (numbers and numbers formatted as text).
        crg.Value = crg.Value
    
    Next Col

    MsgBox "Currency updated.", vbInformation
   
End Sub