Exploring the Benefits of Power Query, Macro VBA for Data Reporting of Supply Chain Management
1.) Freeze Pane
Sub freeze_pane()
'
' freeze_pane Macro
'
' Keyboard Shortcut: Ctrl+j
'
Sheets("Applicant Data").Select
Range("CS2").Select
ActiveWindow.FreezePanes = False
Range("B2").Select
ActiveWindow.FreezePanes = True
Range("A29").Select
End Sub
2.) Spelling mistakes
Sub SpellCheck()
Dim iCell As Range
For Each iCell In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=iCell.Text) Then
iCell.Interior.ColorIndex = 6
End If
Next iCell
End Sub
3.) SAVE worksheet as pdf
Sub save_excel_pdf()
With ActiveSheet.PageSetup
.CenterHeader = "Sample Excel File Saved As PDF"
.Orientation = xlPortrait
.PrintArea = InputBox("Please select cells to convert into PDF")
.PrintTitleRows = ActiveSheet.Rows(5).Address
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:="Sample Excel File Saved As PDF ", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=1, _
To:=5, _
OpenAfterPublish:=True
End Sub
I can't able to use New functions because I'M using 2019 version so Here i mentioned Some New function
Data Summary of All Female Client’s for Income and Loan Amount using New Functions
- Xlookup is a function that allows you to search for a value in a given array and return a result based on the index of the match.
XLOOKUP(lookup_value, lookup_array, [return_array], [match_mode], [search_mode])
- XMATCH is a function that helps you find the position of an item in a group or array of items.
XMATCH(lookup_value,lookup_array, [match_mode])
- SORT is a function that sorts the data in a certain range.
SORT(array, [sort_index], [order])
- UNIQUE is a function that allows you to remove duplicate values from a given range or array.
UNIQUE(array)
- SEQUENCE is a function that generates a sequence of numbers.
SEQUENCE(start, step, [stop])
- VSTACK is a function that stacks data vertically.
VSTACK(array1, array2, ...)
- HSTACK is a function that stacks data horizontally.
HSTACK(array1, array2, ...)