1

This is the default ThisWorkbook code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    FixWidthHeight
End Sub

And this is my FixWidthHeight module:

Sub FixWidthHeight()
    Cells.Select
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
    Application.Goto ActiveCell.EntireRow, True
End Sub

What I want my code to do and in this particular order:

  1. Cells.Select - Selects all cells in the entire worksheet
  2. Selection.Columns.AutoFit - Autofits all column width
  3. Selection.Rows.AutoFit - Autofits all row height
  4. Application.Goto ActiveCell.EntireRow, True - Scrolls the last updated row to the top of the worksheet

The problem I have with this code is that it will not fire the last property Application.Goto ActiveCell.EntireRow, True because the Cells.Select property prevents it from firing. I have tested the code and found Cells.Select to be the culprit. When Cells.Select property is fired, it automatically jumps to the first row on the top of the worksheet and not the last active/updated row. It's as if Cells.Select cancels out Application.Goto ActiveCell.EntireRow, True and that is not what I want. So I would like to know if someone can help me fix this code to obtain my desired results specified. Thank you

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
misner3456
  • 404
  • 2
  • 13
  • You don't need to `Select` to begin with. `Columns.AutoFit`, `Rows.AutoFit` perhaps – BigBen Jul 08 '21 at 01:07
  • @BigBen That won't work because then it won't select the entire worksheet to do AutoFit on. I already tried that btw. – misner3456 Jul 08 '21 at 01:10
  • Perhaps `ActiveSheet.UsedRange.Columns.AutoFit` then. Or `Cells.Columns.AutoFit`. Generally [avoid `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jul 08 '21 at 01:17

1 Answers1

3

You can call AutoFit on the UsedRange.Columns and UsedRange.Rows and avoid Selecting to begin with.

With ActiveSheet.UsedRange
    .Columns.AutoFit
    .Rows.AutoFit
End With

Then the ActiveCell remains unchanged and Application.Goto ActiveCell.EntireRow, True will work as desired.

BigBen
  • 46,229
  • 7
  • 24
  • 40