Pages

Monday, April 30, 2018

msofficekit: How to delete hidden rows in Excel Sheet after filter using Macro

msofficekit: How to delete hidden rows in Excel Sheet after filter using Macro




VBA Code:

Open Excel sheet and Press Alt F11

Click on Insert Button and Insert new module

Paste the below code and save.

Before saving the code you must enable the sheet as Macro Enabled Sheet.

Sub deletehidden()
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

Monday, April 23, 2018

msofficekit: How to create shortcut key to paste special using VBA Code in Excel

msofficekit: How to create shortcut key to paste special using VBA Code in Excel






1. Open active excel sheet 

2. Press Alt + F11 and Visual Basic Application window will open.

3. Go to Insert Menu and select new Module.

4. In new Module insert the below VBA Code and save it.

5. Before do the above process, excel sheet must save as Macro-Enabled.

6. To do Macro Enabled, open Excel Sheet and Click on the Office Button which is at left top corner on excel sheet.

7.  Select Save as and again select Macro Enabled Worksheet and then save it.

VBA Code:


Sub PasteVal()
    Selection.PasteSpecial Paste:=xlValues
End Sub