Can I automatically convert from text to number all over the workbook and autofill blanks until change in number

[ghrek]

Hi

I have a workbook that when downloaded downloads loads of data as text and I need to convert to number.

Issue is it don't download all as text and therefore only need the ones converting that need to. There is no set pattern as to where they are and they all over the workbook.

1) Is there anyway I can auto convert from text to values all over a worksheet?

3) When converted in columns A & B I have a value in lets say cell A1 and then a change of number in A89. What im trying to do is fill in all the blanks with the same number until a change of number and so on all the way down the page for both columns A & B

Any Ideas?

 

[Micron]

One issue is that your data doesn't allow for restricting the ranges you would need to format. Looping over all the cells with data can take some time but it seems to be your only choice. You can test this on a test workbook and see how long it takes. Maybe do it when you're prepared to go for lunch or coffee!
I'm a novice so maybe using 2 methods to find the range isn't ideal. I went this way because I discovered using Find omitted everything to the right of column F in my test sheet, because the last 3 cells in F are blank, so if you have that situation it probably would fail for you too. You can omit header rows by changing Cells(1,1) to Cells(row number,1).

Sub FormatAsNumeric()
Dim rng As range, cel As range
Dim Lcol As Long, Lrow As Long

Lcol = Cells(1, Columns.count).End(xlToLeft).Column
Lrow = Cells.Find(What:="*", After:=range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
         SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
                  
Set rng = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng Is Nothing Then
   Set rng = range(Cells(1, 1), Cells(Lrow, Lcol))
End If

For Each cel In rng
    If Application.WorksheetFunction.IsNumber(cel) Then rng.NumberFormat = "General"
Next

End Sub