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.

 

[=ODIN=]

If I understand correctly, currently

rows().autofit

is doing what you want except that it is resizing the last row of your worksheet -- which you don't want.

So if I can assume that column b will always have a value in it for your last row of data (and that the logo will be one row down from that), and that the logo row should be 50 in height, I would just add another line to your code.

Change the "b" and the 50 to the column and size that suits you best.

    Rows().AutoFit
    Rows(Range("b" & Rows.Count).End(xlUp).Row + 1).RowHeight = 50

 

[rdkapp]

If I understand correctly, currently

rows().autofit

is doing what you want except that it is resizing the last row of your worksheet -- which you don't want.

So if I can assume that column b will always have a value in it for your last row of data (and that the logo will be one row down from that), and that the logo row should be 50 in height, I would just add another line to your code.

Yes, that is correct ODIN!

Change the "b" and the 50 to the column and size that suits you best.

    Rows().AutoFit
    Rows(Range("b" & Rows.Count).End(xlUp).Row + 1).RowHeight = 50

Thank you! That worked! Now, if I may, I need to add a couple new wrinkles into the picture:

1. How do I modify the code to start the AutoFit Row Height on a row other than the top row; say Row 9?
2. On rare occasions, I may need to add a column or 2. When I did that on this one, it no longer worked. Is there a way to add code to handle that situation?

Much appreciated!

 

[=ODIN=]

For Item 1:

Rows will accept a string specifying a range of rows.

[COLOR=#333333][I] Rows("9:50").AutoFit[/I][/COLOR]

would mean rows 9 through 50.

If you wanted to do rows 9 through 50 and row 56 and rows 59-61 just write 3 lines:

[COLOR=#333333][I] 
Rows("9:50").AutoFit
[/I][/COLOR][COLOR=#333333][I]Rows("56").AutoFit
[/I][/COLOR][COLOR=#333333][I]Rows("59:61").AutoFit[/I][/COLOR]

To do 9 through the last row of data is would be

Rows("9:" & Range("b" & Rows.Count).End(xlUp).Row).AutoFit

Sorry, not sure I understand Item 2.
Are you saying that you aren't sure which column will have a entry corresponding to the last row, so you want to check a few to see which has the last row of data?

 

[rdkapp]

To do 9 through the last row of data is would be

Rows("9:" & Range("b" & Rows.Count).End(xlUp).Row).AutoFit

This is exactly what I wanted to do, but the row containing the logo was not expanded (RowHeight=60). I tried adding .RowHeight=60 to the end, but it didn't work, which shows my inexperience. How do I add that to the line to make this work?

Sorry, not sure I understand Item 2.
Are you saying that you aren't sure which column will have a entry corresponding to the last row, so you want to check a few to see which has the last row of data?

No, if I'm understanding your question. Let me try to explain. My initial post stated that I often will have to insert rows into the worksheet. So here, I'm saying that on rare occasions, I may have to insert a column or 2. Currently, the last column with data is "V." When I add a column, the last column with data is now "W." Is there a way to write the code to handle the situation where I add columns?

 

[=ODIN=]

Sorry, you saw my response before I fixed my typo.


The code below will do both -- autofit 9-last row of data, and set logo row to 60

Dim columnLetter As String
columnLetter = "b"
Rows("9:" & Range(columnLetter & Rows.Count).End(xlUp).Row).AutoFit
Rows(Range(columnLetter & Rows.Count).End(xlUp).Row + 1).RowHeight = 60

For item 2 -- it will work with any column that is there when you run it, so if you add a column, you will need to re-run it.
The main assumption is that for any row of data you add, column b will never be blank -- if it will, change the "b" to a column you know will not be blank.

 

[rdkapp]

The code below will do both -- autofit 9-last row of data, and set logo row to 60

Dim columnLetter As String
columnLetter = "b"
Rows("9:" & Range(columnLetter & Rows.Count).End(xlUp).Row).AutoFit
Rows(Range(columnLetter & Rows.Count).End(xlUp).Row + 1).RowHeight = 60

For item 2 -- it will work with any column that is there when you run it, so if you add a column, you will need to re-run it.
The main assumption is that for any row of data you add, column b will never be blank -- if it will, change the "b" to a column you know will not be blank.

That seemed to work; however, when I tested it by adding a column, and then re-running the macro, it became very jumpy as I cursored through the worksheet. Is that because I need to redefine the columnLetter as the last column with data in it?

Here is what I had:

Dim columnLetter As String
columnLetter = "v"
Rows("9:" & Range(columnLetter & Rows.Count).End(xlUp).Row).AutoFit
Rows(Range(columnLetter & Rows.Count).End(xlUp).Row + 1).RowHeight = 60

When I add a column, the last column with data is "w." Do I need to change it to the following:

Dim columnLetter As String
columnLetter = "w"
Rows("9:" & Range(columnLetter & Rows.Count).End(xlUp).Row).AutoFit
Rows(Range(columnLetter & Rows.Count).End(xlUp).Row + 1).RowHeight = 60

 

[=ODIN=]

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


For setting the columnLetter, you should only have to set it the first time you use it, and then never again --unless you add a column in front of the original column.

For instance, lets say you have a table that looks like below, and and cells with data are marked x -- since column c always has data when you add new rows, you would pick columnLetter = "c"

   a   b   c   d   e
1      x   x   x
2      x   x
3      x   x        x
4           x
5           x        x

Even if you a a new column to your table, still c is good.

   a   b   c   d   e   f
1      x   x   x
2      x   x
3      x   x        x 
4           x
5           x        x
6           x   x        x

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.

   a   b   c   d   e   f
1           x   x   x
2           x   x
3           x   x        x
4                x
5                x        x

 

[rdkapp]

I wanted to edit my previous post, but there was no option for it. I guess it times out for editing. So, I'm reposting the last part of my previous post with the edit below. Hopefully, you get what I'm saying.

When I add a column, the last column with data is "w." Do I need to change it to the following:

Dim columnLetter As String
columnLetter = "w"
Rows("9:" & Range(columnLetter & Rows.Count).End(xlUp).Row).AutoFit
Rows(Range(columnLetter & Rows.Count).End(xlUp).Row + 1).RowHeight = 60

Edit: Or is there an easier way to do it?

 

[=ODIN=]

Please double check that you saw my last post -- i may have post while you were posting.


columnLetter does not correspond to the last COLUMN of data. It should be the column which of you are certain will not be blank anytime you add a ROW of data. You only need to change it if you insert a column ahead of it.

So if columnLetter = "v" and you insert a column to the right of v... then no change.
if columnLetter = "v" and you insert a column to the left of v... then yes, now update to "w".