We help IT Professionals succeed at work.

Excel Row Height Autofit

Exl04
Exl04 asked
on
Medium Priority
1,507 Views
Last Modified: 2012-05-11
I have a Access (2007)  table that I export to Excel(2007) .I want to number the records in  column A to the left of each records put their number (1,2,3,4,etc) and also been trying to autofit the row height unsuccessful, what is the method for Excel 2007 VBA? I wrap the text in column F to with= 60, and I want to auto fit to the high of that column text.
Code used below.
Thanks in advance for the input

With Sheet
    iRow=2
Do Until rs.EOF
    
        .Columns("F:F").ColumnWidth = 60
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.AutoFit
        .Rows("1:1000").EntireRow.AutoFit
        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"
        .Column("A").Count
        .Cells(iRow, 1).Value = rs("No")
        .Cells(iRow, 2).Value = rs("Unit")
        .Cells(iRow, 3).Value = rs("Model")
	iRow=iRow+1
	rs.moveNext
Loop
End With

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
your codes always call the formatting at every record read.
* populate first your excel sheet with values
* do the formatting separately after the sheet was populated
  -place your formatting codes outside the loop


what exactly is your problem?

Author

Commented:
Can't format Row height, even when formatting code is outside loop
CERTIFIED EXPERT
Top Expert 2016

Commented:
where are you running the codes?
Top Expert 2007

Commented:
No points, please.

Code like this:


With Sheet
    iRow=2
    Do Until rs.EOF
    
        .Cells(iRow, 1).Value = rs("No")
        .Cells(iRow, 2).Value = rs("Unit")
        .Cells(iRow, 3).Value = rs("Model")
	iRow=iRow+1
	rs.moveNext
    
    Loop
    
    .Columns("F:F").ColumnWidth = 60
    .Range("F:F").WrapText = True
    .Columns("A:F").EntireColumn.AutoFit
    .Rows("1:1000").EntireRow.AutoFit
    .Columns("A:I").Font.Name = Calibri
    .Columns("A:I").Font.Size = 8
    .Columns("D:D").NumberFormat = "mm-dd-yy"
    .Column("A").Count

End With

Open in new window

Author

Commented:
I’m running the code from an Access Form command button.
Badotz: When use you code it breaks in Column(“A”).Count,… so still can check row height

Author

Commented:
Error message said “Object doesn’t support this properly or method”
CERTIFIED EXPERT
Top Expert 2016

Commented:
upload a .mdb version of the db
CERTIFIED EXPERT
Top Expert 2016

Commented:
or post the whole codes
Top Expert 2007

Commented:
>>When use you code it breaks in Column(“A”).Count

So move it out of the loop.

Author

Commented:
Here is code
Private Sub Command0_Click()

    sAppPath = "MyDirectory”
    sDate = Replace(FormatDateTime(Now(), vbShortDate), "/", ".")
    sFileName = sAppPath & "Dbs_Issues As " & sDate & ".xlsx"

    Dim rs As DAO.Recordset, iCol As Integer, iRow As Integer
    Dim xlObj As Object, Sheet As Object
    DoCmd.OpenQuery "issuesAllConcApp_Q"
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add

    Set rs = CurrentDb.OpenRecordset("ConcatIssues_T")

    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")

    For iCol = 0 To rs.Fields.Count - 1
        Sheet.Cells(1, iCol + 1).Value = rs.Fields(iCol).Name
    Next
    rs.MoveFirst


    With Sheet
        iRow = 2
        Do Until rs.EOF

            .Cells(iRow, 1).Value = rs("No")
            .Cells(iRow, 2).Value = rs("Unit")
            .Cells(iRow, 3).Value = rs("Model")
            .Cells(iRow, 4).Value = rs("field1")
            .Cells(iRow, 5).Value = rs("field2")
            .Cells(iRow, 6).Value = CStr(rs("Issues_Description") & "")
            .Cells(iRow, 7).Value = rs("field3")
            .Cells(iRow, 8).Value = CStr(rs("Issues_Comments") & "")
            .Cells(iRow, 9).Value = rs("field3")
            iRow = iRow + 1
            rs.MoveNext
        Loop
        
        .Columns("F:F").ColumnWidth = 60
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.Autofit
        .Range("A1:E1").Rows.Autofit
        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"

    End With

    xlObj.ActiveWorkbook.saveas FileName:=sFileName, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=True

    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing


End Sub

Open in new window

Author

Commented:
I need to number the records per row (1,2,3,4,etc) and autofit rows
CERTIFIED EXPERT
Top Expert 2016

Commented:
<I need to number the records per row (1,2,3,4,etc) and autofit rows>

in what column do you want the numbering ?
if in column A, start the column to 2

dim rowCnt as integer
rowCnt=1

        Do Until rs.EOF


        .Cells(iRow, 1).Value = rowCnt     '< Row counter

        .Cells(iRow, 2).Value = rs("No")
 




            rowCnt=rowCnt + 1

            iRow = iRow + 1
            rs.MoveNext
        Loop




Author

Commented:
Capricorn1 thanks for your advice...
I may not explained correctly; code already accomplish the green area (see pic), all need is enumerate the records (yellow area) when you said start column in 2 I don’t follow you. Also when I set width in column width F then when populate I need rows to auto fit height of text
I need to modify the code already have to enumerate the rows staring in row 2 and autofit all populated rows height.

sheet.bmp
CERTIFIED EXPERT
Top Expert 2016

Commented:
can you upload a copy of the db.. need to see some data(could be the reason for not allowing you to set the rowheight properly, replaced sensitive info with dummy)


CERTIFIED EXPERT
Top Expert 2016

Commented:
..also, do the columns and rows autofit as the last codes to be executed after formatting your data



        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"

        .Columns("F:F").ColumnWidth = 60  ' < might not be needed
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.Autofit
        .Range("A1:E1").Rows.Autofit

Author

Commented:
Colunm F is the main one I need it to be = 60. This column may have lots for text so I got them as Memo in the table and that’s the reason I want to set I tto =60 and I want the row to auto fit the height of the amount of text .
Attached dummyFile
Export.mdb
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That did it, thanks!..
Do you know why even that we set up         .Range("A1:E1").Rows.Autofit I can still see blank space above some cell text or when I have lots of text in these fields some of this text gets hidden at the bottom of cell? Do I need to set a minimum too or what is the trick to make every row auto fit, or better yet is AutoFit the right way to go, is there any other property of Row it be use to accommodate (make all text visible and fit in a columns cells?

Author

Commented:
Thanks for the solution!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.