1) Delete empty rows and empty columns
Have you ever tried to manually delete empty rows and empty columns in a large workbook containing lots of worksheets? I guess you had to spend a lot of time doing that manually. Below you find two macros I have written to do the job in seconds...
The first one will run the second (main) macro in all the worksheets, no matter how many they are or what names they have.
If you use the macros as they are, you will see everything happen on the screen, as the macros run. If you don't want to see the result before the macros have finished, just change the word 'True' to 'False' in the two places below.
1) The macro making the second (main) macro run for every worksheet in the workbook
Sub RunOnEverySheetInWorkbook()
n = Worksheets.Count
For i = 1 To n
Worksheets.Item(i).Select
Call DeleteEmptyRowsAndColumns
Next i
End Sub
2) The main macro doing the job in each worksheet...
Sub DeleteEmptyRowsAndColumns()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = True
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
LastColumn = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = True
For c = LastColumn To 1 Step -1
If Application.CountA(Columns(c)) = 0 Then Columns(c).Delete
Next c
End Sub
2) Password-protect all worksheets in a workbook:
If you want to protect each worksheet in a workbook, no matter how many they are or what names they have, you can use the following macro:
To protect them:
Sub Protect()
For i = 1 To Worksheets.Count
Sheets(i).Protect ("Testpassword")
Next i
End Sub
To unprotect them:
Sub Unprotect()
For i = 1 To Worksheets.Count
Sheets(i).Unprotect ("Testpassword")
Next i
End Sub