' Also bolds the top row, scrolls to the top left corner of the sheet, and autofits columns (with a maximum width so some text columns don’t get ridiculous). ' Active sheet: Prep for quick viewing ' Scroll to top-left corner, freeze top row, bold top row, AutoFit columns Sub SetUp_NiceView() ' Declare variables Dim rowLast As Long Dim colLast As Integer Dim i As Integer ' Maximum column width when AutoFitting columns ' Value needs to be in points (you can see the points when clicking-and-dragging to resize a column) Const maxColWidth As Double = 35.86 ' 256 pixels ' Set up nice view! With ActiveSheet ' Unhide all cells On Error Resume Next .ShowAllData .Cells.EntireRow.Hidden = False .Cells.EntireColumn.Hidden = False On Error GoTo 0 ' Get last row and column ' Excel's Find function remembers the last settings used: Search rows second so the Find function remembers to search by row On Error Resume Next colLast = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column rowLast = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row On Error GoTo 0 ' If you don't want the code to unhide all cells, use these definitions instead: ' colLast = .UsedRange.Columns.Count ' rowLast = .UsedRange.Rows.Count If rowLast = 0 Or colLast = 0 Then Exit Sub ' Bold top row .Range(.Cells(1, 1), .Cells(1, colLast)).Font.Bold = True ' Freeze top row ActiveWindow.FreezePanes = False Application.GoTo .Cells(2, 1), True ActiveWindow.ScrollRow = 1 ActiveWindow.FreezePanes = True .Cells(1, 1).Select ' Disable AutoFilter if it's on .AutoFilterMode = False ' AutoFilter top row With .Range(.Cells(1, 1), .Cells(rowLast, colLast)) .AutoFilter ' AutoFit columns .Columns.AutoFit ' Loop through each column ' If any have exceed the max width, try AutoFitting just the header ' If the column still exceeds the max width, set it to the max width For i = 1 To colLast If .Columns(i).ColumnWidth > maxColWidth Then .Columns(i).Cells(1).Columns.AutoFit If .Columns(i).ColumnWidth > maxColWidth Then .Columns(i).ColumnWidth = maxColWidth End If End If Next i End With End With End Sub
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter