Learn Excel to work faster

There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.

2. Click Insert > Module, and paste the following code in the Module Window.

---

 

• Open the VBE page to press the key Alt+F11
• Insert the module to write the code
• Write the below mentioned code:

---

 

Sub Delete_Columns()

Dim C As Integer

C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

Do Until C = 0

If WorksheetFunction.CountA(Columns(C)) = 0 Then

Columns(C).Delete

End If

C = C - 1

Loop

End Sub

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select aadhar_Count
  Click Run
   

---

 

 
Sub Aadhar_Count()
'
' Aadhar_Count Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    Range("K2").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range("K2:M2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Members"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Aadhar"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Seeding %"
    Range("P10:P11").Select
    Range("P11").Activate
    Columns("K:M").Select
    Selection.ColumnWidth = 20.11
    Range("K2").Select
    ActiveWindow.SmallScroll Down:=-20
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C2:R20000C2,RC[-9])"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(R1C2:R20000C2,RC[-10],R1C10:R20000C10,""*"")"
    Range("L3").Select
    ActiveWindow.SmallScroll Down:=-16
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-1]/RC[-2]%),2)"
    Range("M3").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-1]/RC[-2]%),2)"
    Range("M2").Select
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0.00"
    Range("K2").Select
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("L2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("M2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Columns("K:M").Select
    Selection.ColumnWidth = 7.11
    Range("O5").Select
    ActiveWindow.SmallScroll Down:=-16
    Cells.Replace What:="*/ ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Address"
    Range("N7").Select
    ActiveWindow.SmallScroll Down:=-20
    Columns("C:E").Select
    Selection.ColumnWidth = 12.89
    Selection.ColumnWidth = 15.11
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("D9").Select
    ActiveWindow.SmallScroll Down:=-28
    Columns("J:J").ColumnWidth = 11.78
    ActiveWindow.SmallScroll Down:=-136
    Range("K2:M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N11").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "AAY ?"
    Range("N4").Select
    ActiveWindow.SmallScroll Down:=-52
End Sub

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.

  Test the code:

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select aadhar_Count
  Click Run 

---

 

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.

  Test the code:
  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select aadhar_Count
  Click Run

---

 

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select Delete_Matched
  Click Run
   

---

Sub Delete_Matched()
‘
‘ Delete Macro
With ActiveSheet
.AutoFilterMode = False
With Range(“K1”, Range(“K” & Rows.Count).End(xlUp))
.AutoFilter 1, “Matched”
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
Columns(“K:K”).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range(“L2:M2”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range(“K2”).Select
Cells.Replace What:=”*/ “, Replacement:=””, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:= _
“Unmatched(100-Due to Personal Identity information did not match)”, _
Replacement:=”100-Name”, LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=”Unmatched(998-Invalid Aadhar Number)”, Replacement:= _
“998-Invalid”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=”Unmatched(998-Invalid Aadhaar Number)”, Replacement:= _
“998-Invalid”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=”Unmatched(997-Aadhaar Suspended)”, Replacement:= _
“997-Suspend”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range(“K1”).Select
ActiveCell.FormulaR1C1 = “Status”
Range(“I1”).Select
ActiveCell.FormulaR1C1 = “Address”
Columns(“M:M”).Select
Selection.Delete Shift:=xlToLeft
Range(“L1”).Select
ActiveCell.FormulaR1C1 = “Remarks”
Columns(“A:A”).Select
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “1”
Range(“A3”).Select
ActiveCell.FormulaR1C1 = “2”
Range(“A2:A3”).Select

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Range(“B3”).Select

ActiveWindow.SmallScroll Down:=-4
Columns(“F:F”).Select
Selection.Delete Shift:=xlToLeft
Columns(“G:G”).Select
Selection.Delete Shift:=xlToLeft
Range(“J5”).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = “”
.PrintTitleColumns = “”
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = “”
Application.PrintCommunication = False
Application.PrintCommunication = True

Range(“H1”).Select
ActiveCell.FormulaR1C1 = “Aadhaar”
Range(“J3”).Select

ActiveWindow.SmallScroll Down:=-4
Rows(“1:1”).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“A1:J1”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows(“1:1”).RowHeight = 21
With Selection.Font
.Name = “Arial”
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range(“A1:J1”).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = “$1:$2”
.PrintTitleColumns = “”
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = “”
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “Page &P of &N”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0.078740157480315)
.RightMargin = Application.InchesToPoints(0.078740157480315)
.TopMargin = Application.InchesToPoints(0.078740157480315)
.BottomMargin = Application.InchesToPoints(0.511811023622047)
.HeaderMargin = Application.InchesToPoints(0.275590551181102)
.FooterMargin = Application.InchesToPoints(0.236220472440945)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = “”
.EvenPage.CenterHeader.Text = “”
.EvenPage.RightHeader.Text = “”
.EvenPage.LeftFooter.Text = “”
.EvenPage.CenterFooter.Text = “”
.EvenPage.RightFooter.Text = “”
.FirstPage.LeftHeader.Text = “”
.FirstPage.CenterHeader.Text = “”
.FirstPage.RightHeader.Text = “”
.FirstPage.LeftFooter.Text = “”
.FirstPage.CenterFooter.Text = “”
.FirstPage.RightFooter.Text = “”
End With
Application.PrintCommunication = True
Range(“B2”).Select
ActiveCell.FormulaR1C1 = “F N”
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “S N”
Range(“C4”).Select
Columns(“A:A”).ColumnWidth = 4.33
Columns(“B:B”).ColumnWidth = 4.44
Columns(“C:C”).ColumnWidth = 16.67
Columns(“D:D”).ColumnWidth = 10.11
Columns(“E:E”).ColumnWidth = 11.78
Columns(“F:F”).ColumnWidth = 6.56
Columns(“G:G”).ColumnWidth = 11.44
Columns(“H:H”).ColumnWidth = 10.44
Columns(“J:J”).ColumnWidth = 8.67

Rows(“3:3”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 15
Rows(“3:8677”).EntireRow.AutoFit
Range(“J4”).Select
End Sub

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select Delete_Matched
  Click Run
   

---

Sub Convert_to_one_PDF()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=”C:\Users\Desktop\Allsheets.pdf”, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select Delete_Matched
  Click Run
   

---

Function RemoveSPChar(allc as string)
dim i as integer
for i=1 to len(allc)
CurChar = Asc(Mid(allc,i,1))
Numeric =Mid(allc,1,1)
If CurChar>=65 And CurChar<=90 or CurChar >=97 And CurChar <=122 Or Numeric >=0 And Numeric <=9 Then
NewName = NewName + Mid(allc,i,1)
End If
Next
RemoveSpChar=NewName
End Function

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select Delete_Matched
  Click Run
   

---

‘Function for FIRST NAME

Function firstname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
GoTo ENDLOOP
Else
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
End If
Next i
ENDLOOP:
firstname = NEWTEXT

End Function

‘Function for MIDDLE NAME

Function midname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then

GoTo ENDLOOP
Else
End If
Next i
ENDLOOP:
For i = SP_POS + 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
GoTo ENDLOOP1
Else
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
End If
Next i

ENDLOOP1:
midname = NEWTEXT
End Function

‘Funciton for LAST NAME

Function lastname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
SP_POS = Application.WorksheetFunction.Find(” “, allc, 1)
GoTo ENDLOOP
Else
End If
Next i
ENDLOOP:
For i = SP_POS + 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
sp_pos2 = Application.WorksheetFunction.Find(” “, allc, SP_POS + 1)
GoTo ENDLOOP1
Else
End If
Next i
ENDLOOP1:
For i = sp_pos2 + 1 To Len(allc)
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
Next i
lastname = NEWTEXT
End Function

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select FPS Wise Count

  Click Run

• Download the VBA Code

• FPS_Wise_Count

   

---

Attribute VB_Name = “FPS_Wise_Count”
Sub FPS_Wise_Count()Application.ScreenUpdating = False
Application.EnableEvents = False

Range(“A8:X682”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Range(“J4”).Select
Dim Col As Long, ColCnt As Long, rng As Range
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
End If
ColCnt = 0
For Col = rng.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Columns(Col).EntireColumn) = 0 Then
rng.Columns(Col).EntireColumn.Delete
ColCnt = ColCnt + 1
End If
Next Col

Exits:

Range(“F1:I1”).UnMerge
Range(“F1:G1”).Merge
Range(“H1:I1”).Merge
Range(“H1:I1”).FormulaR1C1 = “Verified Aadhar”
Range(“H2”).FormulaR1C1 = “Family”
Range(“I2”).FormulaR1C1 = “Members”
Range(“J1:K1”).FormulaR1C1 = “Balance to Seed”
Range(“M1:O1”).UnMerge
Range(“L1:M1”).Merge
Range(“L1:M1”).FormulaR1C1 = “Validation Pending”
Range(“L2”).FormulaR1C1 = “Family”
Range(“M2”).FormulaR1C1 = “Members”
Range(“N2”).FormulaR1C1 = “Mismatch Member %”
Range(“O:O”).EntireColumn.Delete
Range(“J3:N4”).Select
Range(Selection, Selection.End(xlDown)).ClearContents
Range(“H1:N3”).Select
Range(Selection, Selection.End(xlDown)).Borders.LineStyle = xlContinuous
Rows(“1:2”).RowHeight = 25
Range(“A1:N2”).Select
With Selection
.Borders.LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.WrapText = True
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns(“C:C”).ColumnWidth = 24.11
Columns(“B:B”).ColumnWidth = 12.56
Columns(“A:A”).ColumnWidth = 4
Columns(“A:A”).Select
With Selection
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.ShrinkToFit = True
End With
Range(“J3”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-6]-RC[-4]”
Range(“K3”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-6]-RC[-4]”
Range(“L3”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-6]-RC[-4]”
Range(“M3”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-6]-RC[-4]”
Range(“J3:M3”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“I3”).End(xlDown).Select
ActiveCell.Offset(0, 1).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range(“D3”).End(xlDown).Select
ActiveCell.EntireRow.EntireRow.Delete
Selection.Delete Shift:=xlUp
Range(“B3”).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range(“A1:C1”).UnMerge
Range(“A1:A2”).Merge
Range(“B1:B2”).Merge
Range(“C1:C2”).Merge
Range(“N1:N2”).Merge
Rows(“1:1”).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“N4”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-7]%”
Range(“N4”).NumberFormat = “0.00”
Range(“N4”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“I4”).End(xlDown).Select
ActiveCell.Offset(0, 5).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range(“A4”, Range(“A4”).End(xlToRight).End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=”=$C4=”””””
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection
.WrapText = False
End With
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(0, 255, 255)
.TintAndShade = 0
End With
Range(“A4”, Range(“A4”).End(xlToRight).End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=”=$N4>10″
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 255, 0)
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range(“A4”, Range(“A4”).End(xlToRight).End(xlDown)).Select
With Selection
.Borders.LineStyle = xlContinuous
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
End With
Range(“D4”, Range(“D4”).End(xlToRight).End(xlDown)).Select
With Selection.Font
.Name = “Arial”
.Size = 11
End With
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = “$1:$3”
.PrintTitleColumns = “”
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.148)
.RightMargin = Application.InchesToPoints(0.088)
.TopMargin = Application.InchesToPoints(0.128)
.BottomMargin = Application.InchesToPoints(0.148)
.HeaderMargin = Application.InchesToPoints(0.07)
.FooterMargin = Application.InchesToPoints(0.07)
.PrintQuality = 300
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = 100
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
End With
Application.PrintCommunication = True
Rows(“4:720”).EntireRow.RowHeight = 20
Range(“D4”).Select
Selection.End(xlDown).Select
ActiveCell.EntireRow.EntireRow.RowHeight = 30
Range(“F9”).Select
Columns(“J:K”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns(“J:K”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“J3”).Select
ActiveCell.FormulaR1C1 = “Family”
Range(“K3”).Select
ActiveCell.FormulaR1C1 = “Members”
Range(“L3”).Select
ActiveCell.FormulaR1C1 = “Family”
Range(“M3”).Select
ActiveCell.FormulaR1C1 = “Members”
Range(“J2:K2”).Merge
Range(“L2:M2”).Merge
Range(“J2:K2”).Select
ActiveCell.FormulaR1C1 = “Y Day Pending”
Range(“L2:M2”).Select
ActiveCell.FormulaR1C1 = “Daily Validation”
Range(“L4”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-2]-RC[4]”
Range(“M4”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=RC[-2]-RC[4]”
Range(“L4:M4”).Select
Selection.Copy
Range(“I3”).End(xlDown).Select
ActiveCell.Offset(0, 3).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Rows(“1:1”).RowHeight = 24.6
Range(“A1:O1”).Merge
Range(“A1:O1”).Select
Range(“A1:O1”).FormulaR1C1 = “FPS WISE AADHAR SEEDING & VALIDATION REPORT OF GANJAM DISTRICT AS ON”
Range(“P1:R1”).Merge
Range(“P1:R1”).Select
ActiveCell.FormulaR1C1 = “=today()”
Range(“A1:O1”, “P1:R1”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Font.Name = “Arial”
.Font.Size = 16
.Font.Bold = True
End With
Range(“B4”).End(xlDown).Font.Size = 14
Columns(“L:P”).ColumnWidth = 6.11
Columns(“Q”).ColumnWidth = 9
Range(“L4”).Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.

2. Click Insert > Module, and paste the following code in the Module Window.

3. Save and close this code and go back the worksheet, in a blank cell, enter this formula =NumberstoWords(A2)( A2 is the cell you want to convert the number to English word), see screenshot:

4. Then press Enter key, and select cell C2 then drag the fill handle to the range that you want to contain this formula. All the numeric values have been converted their corresponding English words.

---

 

• Open the VBE page to press the key Alt+F11
• Insert the module to write the code
• Write the below mentioned code:

---

 

Sub Delete_Columns()

Dim C As Integer

C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

Do Until C = 0

If WorksheetFunction.CountA(Columns(C)) = 0 Then

Columns(C).Delete

End If

C = C - 1

Loop

End Sub

• How to use:
  Copy above code.
  In Excel press Alt + F11 to enter the VBE.
  Press Ctrl + R to show the Project Explorer.
  Right-click desired file on left (in bold).
  Choose Insert -> Module.
  Paste code into the right pane.
  Press Alt + Q to close the VBE.
  Save workbook before any other changes.
    
  Test the code:
   

  Enter some data in random locations on your spreadsheet
  Press Alt + F8 to open the macro dialog box.
  Select DeleteBlankRows
  Click Run
   

---

 

 
Sub DeleteBlankRows() 
    Dim Rw As Long, RwCnt As Long, Rng As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
On Error GoTo Exits: 
     
    If Selection.Rows.Count > 1 Then 
        Set Rng = Selection 
    Else 
        Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row())) 
    End If 
    RwCnt = 0 
    For Rw = Rng.Rows.Count To 1 Step -1 
        If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then 
            Rng.Rows(Rw).EntireRow.Delete 
            RwCnt = RwCnt + 1 
        End If 
    Next Rw 
     
Exits: 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub 
 
Sub DeleteBlankColumns() 
    Dim Col As Long, ColCnt As Long, Rng As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
On Error GoTo Exits: 
     
    If Selection.Columns.Count > 1 Then 
        Set Rng = Selection 
    Else 
        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column())) 
    End If 
    ColCnt = 0 
    For Col = Rng.Columns.Count To 1 Step -1 
        If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then 
            Rng.Columns(Col).EntireColumn.Delete 
            ColCnt = ColCnt + 1 
        End If 
    Next Col 
     
Exits: 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub