Get the weekday in Excel

Use the below formula to get the weekday.

=TEXT(WEEKDAY(NOW()),"dddd")

Code to go to the top cell

VBA Code to go to the top cell in a sheet:

Private Sub Workbook_Open()

With ActiveWindow

.ScrollRow = 1

.ScrollColumn = 1

Call Cells(.ScrollRow, .ScrollColumn).Select

End With

End Sub

Get to last row in a column

How to get to the last row in a column using VBA:

Dim LastRow As Long

LastRow = Range("I" & Rows.Count).End(xlUp).Row

'I is the column and LastRow is a variable

Delete rows using Loop

VBA code to loop thru rows within column and delete rows containing a certain value:

Dim LastRow As Long 

Dim I As Long 

 

LastRow = Range("D" & Rows.Count).End(xlUp).Row 

 

For I = LastRow To 2 Step -1  'Change the row number

    Set rng = Range("D" & I) 

    If rng.Value= "ABC" Then   'Change the condition

        rng.EntireRow.Delete 

    End If 

     

Next I 

Negative closest value to zero:

Get the negative closest value to zero:

Get URL from an hyperlink in Excel

VBA code to get the URL address from a link:

'Extract URL from hyperlink
Function HLink(rng As Range) As String
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Send emails through Outlook using Excel

Here is the VBA code to send emails from Outlook:

Sub SendEmails()
Dim OlApp As Object
Dim OlMail As Object

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(olMailItem)

With OlMail
    .Display
    .To = "aaa@gmail.com"
    .CC = "bbb@gmail.com"
    .Subject = "Test Email"
    .Attachments.Add "C:\Test.xlsx"
    .Body = "Test email"
    .Save
    .Close olPromtForSave
    .Send
End With

End Sub