UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Row Height Customization After Auto Row Height Setting, Office 2013    
 
   
pmcpowell
post Nov 2 2019, 06:31 AM
Post#1



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


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
Go to the top of the page
 
GroverParkGeorge
post Nov 2 2019, 08:15 AM
Post#2


UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pmcpowell
post Nov 2 2019, 08:31 AM
Post#3



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


Yes there is but the cell borders are too close to the text for the client's liking.
Go to the top of the page
 
GroverParkGeorge
post Nov 2 2019, 09:09 AM
Post#4


UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pmcpowell
post Nov 2 2019, 09:58 AM
Post#5



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


Thanks George - I'll give it a try notworthy.gif
Go to the top of the page
 
GroverParkGeorge
post Nov 2 2019, 10:40 AM
Post#6


UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pmcpowell
post Nov 2 2019, 10:59 AM
Post#7



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


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
Go to the top of the page
 
GroverParkGeorge
post Nov 2 2019, 12:06 PM
Post#8


UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pmcpowell
post Nov 4 2019, 08:34 AM
Post#9



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


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
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2019, 09:59 AM
Post#10


UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA


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.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Nov 4 2019, 10:58 AM
Post#11


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
pmcpowell
post Nov 5 2019, 09:15 AM
Post#12



Posts: 117
Joined: 13-January 05
From: Phoeinx, AZ, USA


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

Appreciate your help,
PP
Go to the top of the page
 
cheekybuddha
post Nov 5 2019, 09:18 AM
Post#13


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


PP,

yw.gif

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

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 02:45 AM