Monday, June 27, 2016

VBA Code to clear Content in Entire Sheet

VBA Code to clear an entire sheet in Excel uses the clear contents method, however all that does is clear the data from the cells and not the formatting. To truly clear all the cells and start with a blank canvas you need to use the delete method. Finally we looked at the structure of the VBA script and how to amend it to change your VBA from deleting the contents of cells to deleting the entire worksheet from your Excel workbook.

VBA Code Syntex 

 Sheets(“INSERT WORKSHEET NAME HERE”).Cells.ClearContents  

VBA Code/Macro to Clear Content

 Sub ClearSheet()  
 Sheets(“Sheet1”).Cells.ClearContents  
 End Sub  
OR
 Sub ClearSheet()  
 Sheets(“Sheet1”).Cells.Delete  
 End Sub  
OR
 Sub ClearSheet()  
 Sheets(“Sheet1”).Delete  
 End Sub  

Tuesday, June 21, 2016

VBA code to Delete Blank Columns from Active Worksheet

You can use below VBA code to Delete Blank Columns from Active Worksheet:

Sub Delete_Blank_Columns()

'Step1:  Declare your variables.
    Dim MyRange As Range
    Dim iCounter As Long

'Step 2:  Define the target Range.
    Set MyRange = ActiveSheet.UsedRange
   
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Columns.Count To 1 Step -1
   
'Step 4: If entire column is empty then delete it.
       If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
       Columns(iCounter).Delete
       End If

'Step 5: Increment the counter down
    Next iCounter
   
End Sub

Delete Blank Rows from active worksheet

Wednesday, June 15, 2016

VBA code to Delete Blank Rows from Active worksheet

You can use below VBA code to Delete Blank Rows from Active worksheet.




 Sub Delete_Blank_Rows()  
 'Step1: Declare your variables.  
   Dim MyRange As Range  
   Dim iCounter As Long  
 'Step 2: Define the target Range.  
   Set MyRange = ActiveSheet.UsedRange  
 'Step 3: Start reverse looping through the range.  
   For iCounter = MyRange.Rows.Count To 1 Step -1  
 'Step 4: If entire row is empty then delete it.  
     If Application.CountA(Rows(iCounter).EntireRow) = 0 Then  
     Rows(iCounter).Delete  
     End If  
 'Step 5: Increment the counter down  
   Next iCounter  
 End Sub  
Delete Blank Columns from Active worksheet

 

Monday, June 13, 2016

VBA code to Protect and Unprotect all Worksheets in Active Workbook

You can use below VBA codes to Protect and Unprotect all Worksheets In Active Workbook.

Password is used as "RED" (use password without quotes)

Code to Protect All Worksheets In Active Workbook.

Sub ProtectAllSheets()

'Step 1:  Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Step 3:  Protect and loop to next worksheet
    ws.Protect Password:="RED"
    Next ws

End Sub


Code to Un-Protect All Worksheets In Active Workbook.


Sub UnprotectAllSheets()

'Step 1:  Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Step 3:  Protect and loop to next worksheet
    ws.Unprotect Password:="RED"
    Next ws

End Sub