Excel formula to Find text after last space in string

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

shortcut key for merging cells in excel

Just select the cells you want to merge  and press  Alt+h+m+c

Excel macro to send e-mail with attachments using outlook

  Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
lr = Range("A65536").End(xlUp).Row 'Detects the last row with data
If lr > 1 Then
    Set OutApp = CreateObject("Outlook.Application")
    For i = 2 To lr
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Enter your mail content here"

    With OutMail
        .To = "Enter email address of the receiver"
        .CC = ""
        .BCC = ""
        .Subject = "Enter subject line here"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send
    End With


    Set OutMail = Nothing
    Next
    Set OutApp = Nothing
End If

VBA code to print all Excel files in a selected folder

Sub PrintSpecificWorkbooks()
On Error Resume Next
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim blnTask As Boolean

strPath = C:/Excel Files 'Select your folder path here
If strPath = "" Then
MsgBox "Please select the folder to print"
Exit Sub
End If

If Val(Application.Version) >= 10 Then
blnTask = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
End If
Application.ScreenUpdating = False
' Specify the folder...

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check type of file in the folder and open file.
For Each objFile In objFolder.Files
If objFile.Name Like "*.xlsx" Then 'Select all the xlsx files in the folder
strName = objFile.Name
Application.StatusBar = strName
Workbooks.Open objFile
lr = ActiveSheet.Range("C65536").End(xlUp).Row 'Detects the last row in excel file
ActiveSheet.PageSetup.PrintArea = Range("A1:D" & lr) 'Prints area from A to D
Workbooks(strName).PrintOut
Workbooks(strName).Close savechanges:=False
End If
Next
End Sub

Send mail using google apps script in google sheets

Below function assumes that you have client details in the google sheet and e-mail listed in Column E, it will send the e-mail and then will put X in column F, so that if you run the script again it will not send the e-mail again to the one's already sent to.


function myFunction() {


var sheet = SpreadsheetApp.getActiveSpreadsheet();
var myTab= SpreadsheetApp.getActiveSheet().getName(); //gets the active sheet name
var sstc = sheet.getSheetByName(myTab);
var rowsni = sstc.getDataRange().getValues(); //get the total no. of rows with data in spreadsheet

for (var i = 2; i < rowsni.length; i++) {
if (sstc.getRange(i,6).getValue()=="X" ) //Checks if e-mail has already been sent or not
{}
else{
var toaddress = sstc.getRange(i,5).getValue(); //To which you want to send the email
  var message= "Enter Your message here"
var subject = "Enter subject of e-mail here";
MailApp.sendEmail(toaddress, subject, message, {name: "Enter the sender's name"});}
sstc.getRange(i,6).setValue("X");

}
}

 

VBA code to Select File Open File Dialog with Filter

Sub getfilename()

Dim FileName As String

FileName = Application.GetOpenFilename("CSV files(*.csv),*.csv", , "Select CSV file")

If fname = False Then Exit Sub
Range("D4").Value = fname

End Sub

VBA code to find last used row in a column

Sheets("Sheet1").Range("A65536").End(xlUp).Row

The above code will find last row used in Column A of Sheet1