PDA

View Full Version : unhide\resize enmasse



skeetr1
06-11-2013, 07:30 AM
Received a request this AM to un-hide all cells in sheets of the workbook, and re-size all the cells (sheet and workbook) to fit both column width and row height.

It would be such an issue if the request wasn't for 5000+ files.

mancubus
06-12-2013, 12:44 AM
welcome to VBAX

test with back up files...


Sub run_macro_on_all_files_same_folder()
Dim fDir As String, fPath As String
Dim wb As Workbook, ws As Worksheet

fPath = "C:\MyFiles\" 'change
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"

fDir = Dir(fPath & "*.xls*")
Do While fDir <> ""
Set wb = Workbooks.Open(fPath & fDir)
Call Unhide_AutoFit_Worksheets
wb.Close True
fDir = Dir()
Loop
End Sub




Sub Unhide_AutoFit_Worksheets()
Dim ws As Worksheet

For Each ws In Worksheets
With ws
.Cells.EntireRow.Hidden = False
.Cells.EntireColumn.Hidden = False
.Rows.AutoFit
.Columns.AutoFit
End With
Next
End Sub