Change row heights in multiple, non-adjacent rows

Question

We have a spreadsheet that is an export from Crystal Reports. Between records, the row height is set at "3". Between fields, the row height is set to "1.5".

Is there a way to change all the row heights to another value? So, all "3" row heights would become "5" and all "1.5" would become "5"?

We use O16.

Answer 1

Hi Kevin. I am an excel user like you.

You can change the height of the rows (or columns) by using the Row Height Dialogue Box. Select all of the rows that you want to change. If you want to change all of the rows, select the corner square to the left of Column A (the arrow in the screenshot below). Then right click in the selection and choose Row Height as indicated below.

In the Row Height dialogue box enter the height that you want all of the rows to be and click OK

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Unfortunately, that solution is not viable. The spreadsheet is 30 pages long with 2,300 rows to review. Thanks for your trouble.

Answer 3

It sounded like you wanted to change all of the rows in the spreadsheet to the same row height of 5. I'm thinking my assumption was not correct, and there are rows that you don't want to change that are not the ones between the records or the fields?

There may be another way to use VBA that someone may be able to come up with, but I would use the following work-around:

I am assuming here, then, that the rows you want to change are blank with data rows between them. If so...

Add two helper columns. In the first helper column add a column name (assuming you have column headers) and in the next row put this formula and fill all of the way down. Don't drag that will take forever. Select the first cell with the formula, scroll down to the bottom using the scroll bar on the right, hold down shift and click on the last cell to be filled, then use Ctrl+D to fill the formula all of the way down.

=IF(ISBLANK(A1)=TRUE,1,2)

In the second helper column add a column name and type a 1 in the next row. Then scroll down and use shift to select all of the cells in the column. On the Home ribbon in the Editing section find the icon for fill and on the drop-down menu choose Series...

Make sure Columns is selected and the step value is 1, then click OK. That will number all of your rows in sequence.

Now sort by the first helper column that has 1's and 2's. Select all of the rows that have ones and change the row height as above. Adjust the row heights for the rows with 2's if desired. Then sort again, this time by the second helper column with the sequential order to put you sheet back in the correct order.

Hope this is more what you are looking for.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 4

Re: adjust row height

Run the following VBA code...

'---
Sub MakeThemFive()
Dim Ndx As Long

Application.ScreenUpdating = False
For Ndx = 2300 To 2 Step -1
If Rows(Ndx).RowHeight <= 3 Then
Rows(Ndx).RowHeight = 5
End If
Next
Application.ScreenUpdating = True
End Sub
'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (free excel programs)

Answer 5

WOW! Two great replies back to back. I'll try yours, Nothing Left to Lose (or should I cal you 'Freedom'?) and yours, Rich~M. The winner gets a cookie from Netscape Bakeries.