Column Format Assistance

[bepedicino]

Hello,

My knowledge of VBA is very limited so your assistance would be greatly appreciated. I am running the below VBA which is converting the information that I have in Column "U" to a custom format. Prior to running the VBA the row contains a formula of =IF(A43="","", "090"). After running the macro the resulting value in the field is update to "03/30/900" or blank. Whereas, I need it to be in a text format so that it shows "090" or blank.
---------------------------------------------------------------------------------------

Sub PrepareForUpload()

Const MyTarget = "#N/A" ' <-- change to suit

Dim Rng As Range, DelCol As New Collection, x
Dim i As Long, j As Long, k As Long

' Calc last row number
j = Cells.SpecialCells(xlCellTypeLastCell).Row 'can be: j = Range("C" & Rows.Count).End(xlUp).Row

' Collect rows range with MyTarget
For i = 1 To j
If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
k = k + 1
If k = 1 Then
Set Rng = Rows(i)
Else
Set Rng = Union(Rng, Rows(i))
If k >= 100 Then
DelCol.Add Rng
k = 0
End If
End If
End If
Next
If k > 0 Then DelCol.Add Rng

' Turn off screen updating and events
Application.ScreenUpdating = False
Application.EnableEvents = False

' Delete rows with MyTarget
For Each x In DelCol
x.Delete
Next

' Update UsedRange
With ActiveSheet.UsedRange: End With

' Restore screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True


With Application
.Calculate
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

With ActiveSheet
.Columns.Hidden = False
.Rows.Hidden = False
.UsedRange.Value = .UsedRange.Value
End With

For Each Worksheet In ThisWorkbook.Worksheets
If Worksheet.Name = ActiveSheet.Name Then
Else
Worksheet.Delete
End If
Next Worksheet

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

Columns("F").Delete

Const Ffold As String = "\\Daily - Product Classification Upload\" 'change as required

Dim Fname As String

Fname = "Product Classification Upload"
Fname = Fname & " - " & Format(Date, "yyyymmdd") & ".xlsx"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:=Ffold & Application.PathSeparator & Fname, _
FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = True

End Sub

 

[pbornemeier]

The NumberFormat for the field is apparently set to a type of date format.
On my system if I format a cells as "Short Date" and enter 090 I get 3/30/1900.
To keep the leading zero, set the NumbeFormat for that cell to Text before setting its value

I don't see any reason why your code should change the number format of column U, but this line
Columns("U").NumberFormat = "@"
will set it to text and thus preserve leading zeros

Notes:
Generally all Dim and Const statements are placed at the top of the procedure.
UsedRange does not update until worksheet is saved, so what is this statement accomplishing?: With ActiveSheet.UsedRange: End With

If you open a new workbook what is the default cell formatting setting? It is usually "General"