AutoFit Row Height through last row with data (VBA)

[rdkapp]

I’m using the VBA code below to apply AutoFit Row Height to an entire Workbook.

<i>Rows().AutoFit</i>

At the bottom of each Worksheet in the Workbook, I have a logo in the last cell (i.e. last row, last column). I do not want this row to have the AutoFit Row Height applied. To complicate matters, I quite often have to add rows to the Worksheets, so the range of rows that the AutoFit Row Height is applied will change.

<b>How do I modify the VBA code to apply the AutoFit Row Height only to the rows that have data, excluding the last row with the logo in the last cell?</b>

I am just beginning to learn VBA code and I haven’t been able to find the answer to my issue anywhere. Appreciate any help.

 

[rdkapp]

Sorry, I'm not sure what you meant by "jumpy".

As I cursor through the worksheet, the entire sheet jumps as if the rows are resizing with each cursor move. It was really annoying, but wasn't happening as long as I didn't add a column.

You would only need to change if you added a column in front of c (here i added a column at b, so c became d). now you would need to change columnLetter = "d". They key being that the column you pick has an entry on every row.

FYI - the tables looked good the 1st time you posted. After editing, they now look messed up.

In the future, my worksheet will always have data in every cell of the table. None will be empty. However, for purposes of testing your code, I only put data in cell 18V, the last cell before the row with the logo. Then, I inserted a column at "T" which moved the data in 18V to 18W. I'm guessing that since the code is still looking at column V, and 18V is now empty, the macro was acting weird, resulting in the "jumpy" phenomenon. When I added data in 18V as well, it calmed down with no more jumpiness.

Thank you so much for sticking with me on this. After re-reading your posts and the above paragraph, I think I get it now, but let me make sure. Tell me if I'm correct on the following statement:

It doesn't matter how many columns I add, as long as there is data in Column V on the last row before the row containing the logo. Is that correct?

 

[=ODIN=]

It doesn't matter how many columns I add, as long as there is data in Column V on the last row before the row containing the logo. Is that correct?

If columnLetter = "v" then your statement is 100% correct.

 

[rdkapp]

If columnLetter = "v" then your statement is 100% correct.

Yes, and since I now understand, I will likely move the columnLetter definition up the alphabet a bit to a column that will contain data before filling in any of the other columns.

Thanks again for sticking with me on this, helping me with VBA, and sharing your knowledge.

 

[=ODIN=]

It was my pleasure. I learnt from book/forums, and I try to pay it forward where I can.

 

[MARK858]

@rdkapp, if you don't know what the longest column is then you can use the code below which isn't column specific.

Rows("9:" & Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row).AutoFit