How to quickly hide unused cells, rows and columns in Excel – Scroll area with VBA A1:E50

How to quickly hide unused cells, rows and columns in Excel – Scroll area with VBA A1:E50

The Sub below is assigned with macro to button Limit Scroll to existing rows and columns for ‘Sheet1‘

Watch video on YouTube

Sub UnhideAndUnscrollSheet()

‘Button <<Limit Scroll to existing rows and columns for ‘Sheet1’>>

Dim ws As Worksheet

Dim lastRow As Long

Set ws = ThisWorkbook.Sheets(“Sheet1”)

‘ Unhide all rows and columns

ws.Rows.Hidden = False

ws.Columns.Hidden = False

‘ Set cell B1 to bold

ws.Range(“B1”).Font.Bold = True

‘ Set headers in row 3 to bold

ws.Rows(3).Font.Bold = True

‘ Set all columns to font size 13

ws.Cells.Font.Size = 13

‘ Set cell B1 to font size 16

ws.Range(“B1”).Font.Size = 16

‘ Set all rows and columns to a reasonable height and width

ws.Rows.RowHeight = 15 ‘ You can adjust this value as needed

ws.Columns.ColumnWidth = 20 ‘ You can adjust this value as needed

‘ Get the last row in column B starting from row 4

If Application.WorksheetFunction.CountA(ws.Range(“B4:B” & ws.Rows.Count)) > 0 Then

lastRow = ws.Range(“B4:B” & ws.Rows.Count).Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Else

lastRow = 4 ‘ If there are no data, set lastRow to 4

End If

‘ Clear cells after the last row in column B, C, D, E from row 4 onwards

If lastRow < ws.Rows.Count Then

ws.Range(“B” & (lastRow + 1) & “:B” & ws.Rows.Count).ClearContents

ws.Range(“C” & (lastRow + 1) & “:C” & ws.Rows.Count).ClearContents

ws.Range(“D” & (lastRow + 1) & “:D” & ws.Rows.Count).ClearContents

ws.Range(“E” & (lastRow + 1) & “:E” & ws.Rows.Count).ClearContents

End If

‘ Clear any custom scroll area

ws.scrollArea = “”

‘ Ensure the entire sheet is visible

ws.Activate

ActiveWindow.ScrollRow = 1

ActiveWindow.ScrollColumn = 1

MsgBox “All rows and columns are now visible and scrollable. Cells after the last row in column B (Date) have been cleared.”

End Sub

#coding #excel #scroll #mindstormGR