Row Height when Exporting to Excel

  • Thread starter Thread starter TADropik
  • Start date Start date
T

TADropik

I'm able to export to excel through VBA code with out any problem.

If I write to a cell and the data has no Chr(10) in it, everything is fine:
objWS.Cells(1, 1) = "123 Main Street"

I i write to a cell and the data has Chr(10)'s in it, the Row Height ends up
to big:
objWS.Cells(1, 1) = "123 Main Street" & Chr(10) & _
"Orlando, FL" & Chr(10) & _
"USA"
I can go to the cell, press F2, then enter and it auto fits the Row Height.

Is there a way I can keep the Row Height Auto fitted to the data?
 
xlApp.Cells.Select
xlApp.Cells.EntireRow.AutoFit
xlApp.Cells.EntireColumn.AutoFit
xlApp.Rows("2:2").Select

Use the object of your Excel.Application instead of xlApp.
 
Still didn't work. (below is the code)

Dim objExcel As Excel.Application

objExcel.Cells.Select
objExcel.Cells.EntireRow.AutoFit

However, when a create a Macro in the Excel spreadsheet and run it, it
works. (below is the macro)

' Macro1 Macro
' Macro recorded 2/9/2009 by Me...
'
Cells.Select
Cells.EntireRow.AutoFit
 
Still didn't work (see code sample below)

Dim objExcel As Excel.Application
objExcel.Cells.Select
objExcel.Cells.EntireRow.AutoFit

When I create a Macro in the Excel spreadsheet and run it, it works (see
macro below)

' Macro1 Macro
' Macro recorded 2/9/2009 by Me
'
Cells.Select
Cells.EntireRow.AutoFit
 
Still didn't work (see code sample below)

Dim objExcel As Excel.Application
objExcel.Cells.Select
objExcel.Cells.EntireRow.AutoFit

When I create a Macro in the Excel spreadsheet and run it, it works (see
macro below)

' Macro1 Macro
' Macro recorded 2/9/2009 by Me
'
Cells.Select
Cells.EntireRow.AutoFit
 
TADropik said:
I'm able to export to excel through VBA code with out any problem.

If I write to a cell and the data has no Chr(10) in it, everything is
fine: objWS.Cells(1, 1) = "123 Main Street"

I i write to a cell and the data has Chr(10)'s in it, the Row Height
ends up to big:
objWS.Cells(1, 1) = "123 Main Street" & Chr(10) & _
"Orlando, FL" & Chr(10) & _
"USA"
I can go to the cell, press F2, then enter and it auto fits the Row
Height.

Is there a way I can keep the Row Height Auto fitted to the data?
Click to expand...

After entering the information, on the same cell, try AlexM's
suggestion

objWS.Cells(1, 1) = "123 Main Street" & Chr(10) & _
"Orlando, FL" & Chr(10) & _
"USA"
objWS.Cells(1, 1).EntireColumn.AutoFit

or

With objWS.Cells(1, 1)
.Value = "123 Main Street" & Chr(10) & _
"Orlando, FL" & Chr(10) & _
"USA"
.EntireColumn.AutoFit
End With
 
Back
Top