Wrap Text

[markh1182]

If in a spreadsheet you need to count down 60 rows, when someone wraps text and it goes onto a second line, is there a way to get a macro to count the 2nd line as a row, as therefore instead of going to row 60, it would count to row 59?

 

[patrickmuldoon99]

Hmmm

I would have thought so, yes, but it would require you to search each string in the cells for a carriage return character....

 

[gaj104]

How about using the rowheight property? Assuming the rows are equal heights (default 12.75)

Sub Macro1()

tHeight = 0
For n = 1 To 60
x = Range("A" & n).RowHeight
tHeight = tHeight + x
Next

tHeight = (tHeight / 12.75)

'rows to exclude
exRows = tHeight - 60

Range("A1:A" & (60 - exRows)).Select


End Sub

Hope it helps

PS excuse my messy coding!!

 

[markh1182]

That is just the sort of macro I wanted.
Ta very much

 

[Richard Schollar]

Gaj - that's an interesting way of tackling the problem. It is then dependent on the sheet formatting. Mark, maybe it would help us if you could explain exactly why you are wanting to do this?

 

[markh1182]

This needs to be done as the I need to be able to set the page setup correctly. The users want to be able to wrap text, but every equivalent to 60 rows height I need to put Page Summary at the bottom of the page and have a page break as this spreadsheet needs to be set in a specific style.