VBA excel Macros info

To Find the Last Row.

Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

 VBA to automatically determine what your column widths should be.  

https://www.thespreadsheetguru.com/the-code-vault/2014/3/25/vba-code-to-autofit-columns

GRC AC 10 - Create new mitigating control owners

GRC AC 10 - Create new mitigating control owners

Create User in SU01 in GRC

Run the user sync à Object Repository

NWBC à  Access Management/Setup à Access Control Owners à create an entry and select owner type

NWBCàMaster Data à OrganizationsàAssign user in Owner Tab

NWBC à Master Data à Mitigating Controls à Select & Open Control à Owners Tab à Add Row

VBA Excel Links

Delete Empty Row. 

checks entire row and if empty then delete

https://www.mrexcel.com/forum/excel-questions/901824-vba-delete-blank-rows-only.html

RDDIT076 - change released TR into Unreleased TR

change released TR into Unreleased TR 

SE38 --> RDDIT076

VBA Excel for Login

Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer


Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
            (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Global Const SW_MAXIMIZE = 3
Global Const SW_SHOWNORMAL = 1
Global Const SW_SHOWMINIMIZED = 2

===============

Dim MyURL As String
 On Error GoTo Err_Clear
 MyURL = "https://infinity.icicibank.com/corp/AuthenticationController?FORMSGROUP_ID__=AuthenticationFG&__START_TRAN_FLAG__=Y&FG_BUTTONS__=LOAD&ACTION.LOAD=Y&AuthenticationFG.LOGIN_FLAG=1&BANK_ID=ICI"
 Set MyBrowser = New InternetExplorer
 MyBrowser.Silent = True
 MyBrowser.navigate MyURL
 apiShowWindow MyBrowser.hwnd, SW_MAXIMIZE
 MyBrowser.Visible = True
 Do
 Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
 Set HTMLDoc = MyBrowser.document
 'HTMLDoc.all.CorporateSignonCorpId.Value = "" 'Enter your email id here
 'HTMLDoc.all.CorporateSignonPassword.Value = "" 'Enter your password here

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
 If MyHTML_Element.ID = "AuthenticationFG.USER_PRINCIPAL" Then MyHTML_Element.Value = "ICICIUSERID": Exit For
 Next


 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
 If MyHTML_Element.ID = "AuthenticationFG.ACCESS_CODE" Then MyHTML_Element.Value = "ICICICPASSWORD": Exit For
 Next

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
 If MyHTML_Element.ID = "VALIDATE_CREDENTIALS1" Then MyHTML_Element.Click: Exit For
 Next

 Application.Wait Now + TimeValue("00:00:10")

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("a")
 If MyHTML_Element.ID = "Bank-Accounts" Then MyHTML_Element.Click: Exit For
 Next


 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("a")
 If MyHTML_Element.ID = "HREF_actNicNameOutput[0]" Then accName = MyHTML_Element.Value: Exit For
 Next

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("a")
 If MyHTML_Element.ID = "HREF_actNoOutput[0]" Then AccNo = MyHTML_Element.Value: Exit For
 Next

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName("a")
 If MyHTML_Element.ID = "HREF_actBalOutput[0]" Then AccBal = MyHTML_Element.Value: Exit For
 Next


Err_Clear:
 If Err <> 0 Then
 Err.Clear
 Resume Next
 End If

Excel Tips

1. Sum: Alt +  '+'
2. Time: Ctrl + Shift + ;
3. Date : Ctrl + ;
4.Row Highlight : Shift+ Space Bar
5.Column Highlight: Ctrl + Space Bar
6.Removing Column: Ctrl + 0
7.Removing Row: Ctrl + 9
8.New Line In a Cell: Alt + Enter
9.Show/Hide Formula: Ctrl + ~
10.Quick Filter: Alt + Down
11.Filter All: Ctrl + Shift + L
12.Sort Filter: Alt + A + SS
13.Save As: F12
14.Text to Columns : Alt + A + E
15.Remove Duplicates : Alt + A + M
16.Data Validation: Alt + A + V
17.Share a Spread Sheet: Alt + R + W
18.Show All Comments: Alt + R + A
19.Protect Sheet: Alt + R + PS
20.Protect WorkBook: Alt + R + PW
21.Spell Check: Alt + R + S
22.Arrange All:  Alt + W + A
23.Freeze Panes: Alt + W + FF
24.Zoom In: Ctrl + Mouse
25.

Peg System

• 0—Zoo
• 1—Tie
• 2—Noah
• 3—Ma
• 4—Rye
• 5—Law
• 6—Shoe
• 7—Cow
• 8—Ivy
• 9—Bee

• 0 = Z or S. Remember ‘zero’ starts with a z/s sound
• 1 = T or D. When we write t and d, they both have just one vertical stroke
• 2 = N. N has two vertical strokes
• 3 = M. M has three vertical strokes
• 4 = R. The word ‘four’ ends in R
• 5 = L. The Roman numeral for 50 is L. Simply drop the zero to give 5
• 6 = Ch, Sh or J. The number 6 looks kind of a back to front J
• 7 = K, hard C or hard G. I just remember this one because seven seems like a very sharp, harsh number to me as do these consonants
• 8 = F. A cursive ‘f’ has two loops, just like the number eight
• 9 = P or B sounds. 9 looks like a back to front P