• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for adding specific height to each row in table

A

AndrewH

New Member
Hello,

I am hoping someone out there has a solution for my problem. I have a table that contains text fields in excess of 900 characters. While they look fine in the workbook, they end up "trimming" the bottom 1-4 rows when printing or saving to pdf. Thus, I often have to go in an manually adjust the height for some (but not all) of my rows. What I would like to do is add a line of code that would increase the size of all rows by x pixels, but have yet to figure out how to do that. Any ideas? I would attach the workbook, but there is company sensitive material in there I cannot share.


Thanks!!
 
Perhaps something like this...


Sub TestSize()


'Set Variables

Dim cellHeight As Double

Dim cellwidth As Double

' Read the height and width of cell A1

cellHeight = Range("a1").Height

cellwidth = Range("a1").Width


'Tell you what the height and width is.

MsgBox "The height is: " & cellHeight

MsgBox "the width is: " & cellwidth


'Change the value of the height and width

cellHeight = cellHeight + 20

cellwidth = cellwidth + 20

'Set A1 to the new height and width

Range("a1").RowHeight = cellHeight

Range("a1").ColumnWidth = cellwidth


End Sub


This worked for me. The basics are here I think. If you ran a test on your cells to determine if the contents are a certain size, then run a resize cell routine if true. I am new to VBA, so there might be a better solution out there. If you want to resize ALL of the rows, you can use: range("a:a").RowHeight = value
 
That worked nicely for one row at a time. Thank you!! Any suggestions for having it correct the cell height for all rows in my table?


Thanks again!!
 
Did you try range("a:a").RowHeight = value

This will set the height of all the cells in a spreadsheet to whatever value is set to.
 
Each row in the table will be different, though. I am probably missing something easy here, but putting the range as A:A didn't seem to work...
 
I'm looking into a way of doing this. It would be nice if Excel had an Autofit property.


But for doing all cells try this...


Cells.Select

Selection.RowHeight = value
 
Hi, AndrewH!


Try with this modification to Phuzzy's code:


-----

[pre]
Code: 
Sub TestSize()

'Set Variables
Dim cellHeight As Single
Dim I As Long
Dim rng As Range

' Read the height of cells in column A
I = 1
Do Until Len(Cells(I, 1).Value) = 0
Cells(I, 1).Select
'Read the height of cell A<I>, where <I> is row number
cellHeight = Selection.RowHeight
'Change the value of the height
cellHeight = cellHeight + 20
'Set A<I> to the new height
Set rng = Rows(Selection.Row)
rng.RowHeight = cellHeight
'Loop
I = I + 1
Loop

End Sub
[/pre]
-----


Regards!
 
@Phuzzy

Hi!

I simply added my two-cents to your buck. You did the dirty job, I just arrived at dessert-time. Good job!

Regards!
 
I like where this is going, and you both are helping me a ton. SirJB7, that particular code seems to only change Row 1, how would I get it to change all rows in my table?
 
Hi, AndrewH!

It changes all rows starting from 1 until an empty cell is found in column A. Please note the "I = I + 1" before the "Loop", that increments the row to be processed when looping ("Do Until..." and "Cells(I , 1).Select").

If you have empty cells in column A used for spacing, separations or so, consider typing a space in it, so as to let the loop go on down towards your data's end.

Let us know how it results.

Ah, and don't forget thanking Phuzzy again, he/she deserves all the credit (well, actually 98%, I added my two-cents) ;)

Regards!
 
SirJB7 and Phuzzy, thank you very much! This works!


One last question, if I wanted it to start in row 8, what would I need to change? The top 7 rows of this sheet need to stay "pristine."


Thanks again, you two are my heroes!!!
 
@Phuzzy


Hi!

[pre]
Code: 
For Each AvailableTime In FromNowOn
If NewToVBA Then
Do Until 1 <> 1
ComeBack WhenEverNeeded, WhenEverWanted
GoOnHelping WhoEverAsked
DoEvents 'just in case you want to press Ctrl-C for breaking and saying good-bye
Loop
Else
NewToVBA = Not (NewToVBA)
End If
Next
[/pre]

Regards!
 
@SirJB7


That's pretty awesome. Thanks. And I am a he not a she.


I'll post my little bit of VBA awesomeness here soon. People might find it useful.


Oh, and thanks for making me look up a Single. I didn't know that that datatype existed.
 
@Phuzzy

Hi, he!

We're always eager to get our hands into new stuff, as well as trying to help, so welcome.

Regards!
 
You must log in or register to reply here.
Back
Top