Marco to convert numbers stored as text to numbers and dates stored as text to numbers

for macro you can do

Sub convert()

'Numbers
For Each c In Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row)
c.NumberFormat = "0.00"
Next c

For Each c2 In Range("C4:C" & Range("C" & Rows.Count).End(xlUp).Row)
c.NumberFormat = "0.00"
Next c2

For Each c3 In Range("E4:E" & Range("E" & Rows.Count).End(xlUp).Row)
c.NumberFormat = "0.00"
Next c3

    'Dates
    For Each cell In Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row)
    cell.NumberFormat = "m/d/yyyy"
    Next cell
    
    For Each cell2 In Range("D4:D" & Range("D" & Rows.Count).End(xlUp).Row)
    cell.NumberFormat = "m/d/yyyy"
    Next cell2
End Sub

Hi a2424,

Try this:

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 4 'Static start row number for the data. Change to suit.

    Dim lngEndRow As Long
    Dim lngMyRow As Long
    
    lngEndRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For lngMyRow = lngStartRow To lngEndRow
        Range("A" & lngMyRow) = Val(Range("A" & lngMyRow))
        Range("B" & lngMyRow) = DateValue(Range("B" & lngMyRow))
        Range("C" & lngMyRow) = Val(Range("C" & lngMyRow))
        Range("D" & lngMyRow) = DateValue(Range("D" & lngMyRow))
        Range("E" & lngMyRow) = Val(Range("E" & lngMyRow))
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Lovely Autumn Sydney day isn't it!!

Regards,

Robert

how can I get the marco to run around the blank value

Like this:

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 4 'Static start row number for the data. Change to suit.

    Dim lngEndRow As Long
    Dim lngMyRow As Long
    
    lngEndRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For lngMyRow = lngStartRow To lngEndRow
        If Len(Range("A" & lngMyRow)) > 0 Then Range("A" & lngMyRow) = Val(Range("A" & lngMyRow))
        If Len(Range("B" & lngMyRow)) > 0 Then Range("B" & lngMyRow) = DateValue(Range("B" & lngMyRow))
        If Len(Range("C" & lngMyRow)) > 0 Then Range("C" & lngMyRow) = Val(Range("C" & lngMyRow))
        If Len(Range("D" & lngMyRow)) > 0 Then Range("D" & lngMyRow) = DateValue(Range("D" & lngMyRow))
        If Len(Range("E" & lngMyRow)) > 0 Then Range("E" & lngMyRow) = Val(Range("E" & lngMyRow))
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

You're welcome. Thanks for the feedback and marking the thread as solved