VBA way to remove unused cells and columns from Excel File

Hello Experts,
my project is almost finished but I still have some questions. I d like to VBA remove all unused cells and columns from enclosed Excel 2016 file so just an attached picture stays and controls.
NickAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Alex,

Before continue further, please take a minute to mark all your question as Solved. You are overlooking our request.

Now to your question:
Place the following code on ThisWorkbook Module.
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim lr As Long, lc As Long
Dim shp As Shape

Set ws = Sheets("Sheet1")

lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For Each shp In ws.Shapes
    If shp.BottomRightCell.Row > lr Then
        lr = shp.BottomRightCell.Row
    End If
    
    If shp.BottomRightCell.Column > lc Then
        lc = shp.BottomRightCell.Column
    End If
Next shp
lr = lr + 1
lc = lc + 1

ws.Range(ws.Cells(lr, 1), ws.Cells(lr, 1).End(xlDown)).EntireRow.Hidden = True
ws.Range(ws.Cells(1, lc), ws.Cells(1, lc).End(xlToRight)).EntireColumn.Hidden = True
End Sub

Open in new window

0
 
NickAuthor Commented:
I marked other questions as solved Subodh. Are you sure those questions are still open?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes, all your questions are still open. Visit the link what Martin shared to know how to accept an answer to close your question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.