[X]My Assistant

[pmcpowell]

Hi - I've automated a spreadsheet which takes a raw data excel download and formats the appearance by setting column widths, Font type and size, applies filter drop-downs, Print Area, Repeat Rows at Top and sets automatic Row Height. However the client thinks the Row Height is too scrunched and wants a bit more space top and bottom of the row. In Ms Word I would set this in "Paragraph" Before and After, but there is not that option in Excel. I tried enlarging the font size before the Auto Row Height line of code and then putting it back afterwards but this isn't ideal. I can't hard-code the Row Heights as they contain wrapped text and are not all the same number of characters.

Any help would be greatly appreciated.
Thanks,
Peter

[GroverParkGeorge]

Isn't there an .Autofit property for rows and columns?

[pmcpowell]

Yes there is but the cell borders are too close to the text for the client's liking.

[GroverParkGeorge]

I seem to remember a problem of that sort from a project several years ago, although I don't recall how we resolved it, unfortunately.

It also sounds like the client, though, may be expressing preference for a less standard view of the data. That might call for an additional step in your function that loops through all of the populated cells and increases their size by some small amount.

Sort of like:

Rows("1:1").RowHeight =Rows("1:1").RowHeight + 2
Rows("1:1").ColumnWidth =Rows("1:1").ColumnWidth +2

Moving Cell by Cell would be slow and possibly a bit inconsistent, but I think it should move the ball in the right direction.

Perhaps there is also a way to adapt that to work on whole rows and whole columns at a time, rather than Cell by Cell.

[pmcpowell]

Thanks George - I'll give it a try

[GroverParkGeorge]

If it helps (and it probably won't) I finally remembered that part of our problem with unacceptable formatting came into play when the client upgraded from an earlier version of Excel to a later version, probably 2003 to 2007. A lot of our custom formatting went a bit wonky and required tweaking after the upgrade. I wonder if you are using an Excel template and maybe that's a starting point to make the transition smoother. It's just a thought, though.

[pmcpowell]

Interesting - the raw data is produced in an Excel 97-2003 .xls, but I then copy and paste it into an Office 365 .xlxm workbook.
I would have thought that this would overwrite the old formatting but who knows.

Thanks again,
Peter

[GroverParkGeorge]

I wonder, for the sake of thorough testing, if you can apply the formatting code to the original file and see if it comes out any different.

[pmcpowell]

Hi George - Had internet down in the Hotel most of the weekend so I struggled through the coding without online help. This is the code I came up with which works just fine though not very elegant. I really wanted to do a "For Each" in the range instead of looping through after determining the number of rows but hey, it works. Any suggestions to improve would be great.
Sub Mcr_Auto_Row()
'
' Mcr_Auto_Row Macro
'
Dim EndRow As String
Dim LastPos As Integer
Dim EndCol As String
Dim CurrRow As Integer
Dim RowHt As Double
'-----------------------------------------------
ActiveCell.SpecialCells(xlLastCell).Select 'Find Row Number of Last Row containing Data
EndRow = ActiveCell.Address
EndCol = Mid(EndRow, 2, 1)
'Debug.Print EndCol
LastPos = InStr(1, EndRow, EndCol)
EndRow = Right(EndRow, LastPos)
'Debug.Print EndRow
'-------------------------------------------------
CurrRow = 3 'Loops through all rows containing data and adds 5 point to the Row Height
Do While CurrRow <= EndRow
Range("A" & CurrRow).Select
Selection.EntireRow.Select
RowHt = Selection.RowHeight
Selection.RowHeight = RowHt + 5
CurrRow = CurrRow + 1
Loop
Range("A2").Select

End Sub

[GroverParkGeorge]

That's about what I envisioned, unfortunately. Not much I can see to improve it, but there may be folks with more Excel experience who can comment.

[cheekybuddha]

One suggestion to improve the code is to avoid using the Selection object - it is unnecessary here.

To tidy up you can do something more like:

CODE

Sub Mcr_Auto_Row()
'
' Mcr_Auto_Row Macro
'
  Dim ws As Worksheet
  Dim StartRow As Integer
  Dim EndRow As String
  Dim i As Integer
'-----------------------------------------------
  Set ws = ActiveSheet
  StartRow = 3
  ws.Cells(StartRow, 1).SpecialCells(xlLastCell).Select 'Find Row Number of Last Row containing Data
  EndRow = ActiveCell.Row
  Debug.Print EndRow
'-------------------------------------------------
  If EndRow >= StartRow Then
    With ws
      For i = StartRow To EndRow
        With .Rows(i)
          .RowHeight = .RowHeight + 5
        End With
      Next i
    End With
  End If
  Set ws = Nothing

End Sub

Try to limit your calls to ActiveBook/ActiveSheet/ActiveCell etc and use an object variable instead - the Active* objects can be flaky.

hth,

d

[pmcpowell]

Many thanks CheekyB
That's a nice tidy bit of code.

Appreciate your help,
PP

[cheekybuddha]

PP,



Remember to ask if there is anything that is unclear or which you don't understand.



d