CODEWITHSUNDEEP
vbaexcel
Meesha
Meesha

Reputation: 821

Changing the format of excel turns file in to insane size

I Just change format of sheets using a vba code and the file turns insanely large and increases by ~200 MB. If I do it using excel interface , nothing like this happens but I want to use the code.

Function ReturnName(ByVal num As Integer) As String
    ReturnName = Split(Cells(, num).Address, "$")(1)
End Function     

lcol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
For i = 1 To lcol
If (ws1.Cells(4, i) = "Date") Then
ws1.Range(ReturnName(i) & "5" & ":" & ReturnName(i) & "1000").NumberFormat = "yyyy-mm-dd"
Else
ws1.Range(ReturnName(i) & "5" & ":" & ReturnName(i) & "1000").NumberFormat = "0"
End If
Next i

Upvotes: 0

Views: 66

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Try this for example:

ws1.Range(ReturnName(i) & "5").EntireColumn.NumberFormat = "yyyy-mm-dd"

Upvotes: 2

Related Questions