Macro - changing cell format from number to date

[jazzD]

i have been using the below macro to format an excel sheet with lots of different columns/information. it was working good and i redid it today to include an extra column that i had deleted previously. column A is a date format and column F is numbers (gross amount) but for some reason when i run it it changes the format of Column F to date format and i dont know where this is coming from. ive re-recorded it mulitple time but i cant figure out why this is happening?


Sub Macro5()
'
' Macro5 Macro
'


'
Range("A1:V3").Select
Selection.EntireRow.Delete
Range("A:A,C:C,D:D,G:H,J:L,O:V").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Style = "Currency"
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:F2025").Select
With Selection.Font
.Name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Size = 11
Selection.Font.Size = 12
Selection.Font.Size = 14
Selection.Font.Size = 16
Selection.Font.Size = 18
Selection.Font.Size = 20
Columns("A:A").Select
Range("A2").Activate
Selection.NumberFormat = "m/d/yyyy"
Range("F11").Select
Dim lr As Long
Dim r As Long
Dim sr As Long
Dim sc As String

'***************************************************
' Specifiy the column you wish to apply this to
sc = "B"
' Specify first row of data
sr = 2
'***************************************************

Application.ScreenUpdating = False

' Find last row with data
lr = Cells(Rows.Count, sc).End(xlUp).Row

' Loop through all rows backwards
For r = lr To (sr + 1) Step -1
' Insert row if cell is different than cell above
If Cells(r, sc) <> Cells(r - 1, sc) Then Rows(r).Insert
Next r

Application.ScreenUpdating = True



End Sub

 

[Fluff]

This line of code is what is causing the problems

Selection.Merge

Merged cells are best avoided like the plague as they will cause you no end of problems.
Try using this instead

    Selection.HorizontalAlignment = xlCenterAcrossSelection

 

[jazzD]

This line of code is what is causing the problems

Selection.Merge

Merged cells are best avoided like the plague as they will cause you no end of problems.
Try using this instead

    Selection.HorizontalAlignment = xlCenterAcrossSelection

thank you! yes that cleared up the date issue but now it doesnt merge & centre the top row - but thats an easy step to do after the fact!

 

[Fluff]

As I said It's best to avoid merged cells, the code I showed will give a similar result in terms of what it looks like, without the problems.

 

[jazzD]

As I said It's best to avoid merged cells, the code I showed will give a similar result in terms of what it looks like, without the problems.

ok great! thanks!

 

[Fluff]

You're welcome & thanks for the feedback