Cell Selection of Contiguous Data

Select the Last Cell of a Column of Contiguous Data

To select the last cell in a contiguous column D, use the following example:

ActiveSheet.Range("D1").End(xlDown).Select



Select the Blank Cell at Bottom of a Column of Contiguous Data

To select the cell below a range of contiguous cells, use the following example:

ActiveSheet.Range("E1").End(xlDown).Offset(1,0).Select



Select an Entire Range of Contiguous Cells in a Column

To select a range of contiguous cells in a column, use one of the following examples:

ActiveSheet.Range("B1", ActiveSheet.Range("B1").End(xlDown)).Select

   -or-

    End(xlDown).Address).Select

   -or-

   End(xlUp).Address).Select

   ActiveSheet.Range("A1").End(xlDown).End(xlToRight)).Select

   -or-

   ActiveSheet.Range("A1").End(xlDown).End(xlToRight).Address).Select

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

    -or-

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1:" & ActiveSheet.Cells(lastRow, lastCol).Address).Select


When this code is used with the sample table, cells A1 through A4 will be selected.

How to Select an Entire Range of Non-Contiguous Cells in a Column

To select a range of cells that are non-contiguous, use one of the following examples:

ActiveSheet.Range("A1",ActiveSheet.Range("A<lastRowInSheet>").End(xlUp)).Select


ActiveSheet.Range("A1:" & ActiveSheet.Range("A<lastRowInSheet>"). _


How to Select a Rectangular Range of Cells

In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method:

ActiveSheet.Range("A1").CurrentRegion.Select

In some instances, you may want to select cells A1 through C6. The following examples will select all of the cells:

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

SCOPE OF ON ERROR... STATEMENT

The effect of ON ERROR ... ends as soon as one of the following is encountered:

1. Another ON ERROR .... (Maybe in the form of ON ERROR RESUME x or ON ERROR GOTO x)
2. Exit Sub / Exit Function within the same sub/function where defined.
3. End Sub / End Function of the sub/function where defined.

IS IT BAD TO USE ON ERROR RESUME NEXT?

Yes and No.

Don't use without knowing what the effect of this statement would be. Avoid if possible. Keep the scope short wherever not possible.

To nullify the effect of an ON ERROR RESUME NEXT statement, you can call ON ERROR GOTO 0

How to Select the Intersection of Two or More Specified Ranges

To select the intersection of the two named ranges "Address" and "Contact," you can use the following example:

Application.Intersect(Range("Address"), Range("Contact")).Select

Note that both ranges must be on the same worksheet for this example to work.

How to Select the Union of Two or More Specified Ranges

To select the union (that is, the combined area) of the two named ranges "Address" and "Contact," you can use the following example:

Application.Union(Range("Address"), Range("Contact")).Select

Note that both ranges must be on the same worksheet for this example to work. Union method does not work across sheets. For example, this line works fine

Set y = Application.Union(Range("Sheet1!E5:F6"), _   Range("Sheet1!I9:J10"))

but this line

Set y = Application.Union(Range("Sheet1!E5:F6"), _   Range("Sheet2!I9:J10"))

returns the error message:

Union method of application class failed

How to Select a Specified Range, Offset It, and Then Resize It

To select a range six rows below and six columns to the right of the named range "Address" and include three rows and three column more than the named range, you can use the following example:

Range("Address").Select
Selection.Offset(6, 6).Resize(Selection.Rows.Count + 3, Selection.Columns.Count + 3).Select

How to Select a Specified Range and Resize the Selection

To select the named range "StudentDetails" and then extend the selection by six rows, you can use the following example:

Range("StudentDetails").Select
Selection.Resize(Selection.Rows.Count + 6,Selection.Columns.Count)_
    .Select

How to Select a Range of Cells Offset from a Specified Range

To select a range of cells that is the same size as the named range "NewRecords" but that is shifted six rows down and six columns to the right, you can use the following example:

ActiveSheet.Range("NewRecords").Offset(6, 6).Select

If the named range is on another (not the active) worksheet (Sheet6), activate that worksheet first, and then select the range using the following example:

Sheets("Sheet6").Activate
ActiveSheet.Range("NewRecords").Offset(6, 6).Select

How to Select a Cell Relative to Another (Not the Active) Cell

To select a cell that is six rows below and six columns to the right of cell F6, you can use either of the following examples:

ActiveSheet.Cells(6, 6).Offset(6, 6).Select

ActiveSheet.Range("F6").Offset(6, 6).Select

How to Select a Cell Relative to the Active Cell

To select a cell that is three rows below and three columns to the left of the active cell, you can use the following example:

ActiveCell.Offset(3, -3).Select

To select a cell that is six rows above and six columns to the right of the active cell, you can use the following example:

ActiveCell.Offset(-6, 6).Select

Note An error will occur if you try to select a cell that is "off the worksheet." The first example shown above will return an error if the active cell is in columns A through C, since moving three columns to the left would take the active cell to an invalid cell address.

How to Select a Named Range on a Worksheet in a Different Workbook

To select the named range "Records" on a worksheet (Sheet 5) in a different workbook (NewBook.xlsx), you can use the following example:

Application.Goto Workbooks("NewBook.xlsx").Sheets("Sheet5").Range("Records")

Or, you can activate the worksheet, and then use method 7 above to select the named range:

Workbooks("NewBook.xlsx").Sheets("Sheet5").Activate
Range("Records").Select

How to Select a Named Range on Another Worksheet in the Same Workbook

To select the named range "Records" on another worksheet (Sheet2) in the same workbook, you can use the following example:

Application.Goto Sheets("Sheet2").Range("Records")

Or, you can activate the worksheet, and then use method 7 above to select the named range:

Sheets("Sheet2").Activate
Range("Records").Select

How to Select a Named Range on the Active Worksheet

To select the named range "StudentDetails" on the active worksheet, you can use either of the following examples:

Range("StudentDetails").Select

Application.Goto "StudentDetails"

How to Select a Range of Cells on a Worksheet in a Different Workbook

To select the range E5:J10 on a worksheet (Sheet5) in a different workbook (Book5.xlsx), you can use either of the following examples:

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5:J10")

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Workbooks("BOOK5.xlsx").Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Range of Cells on Another Worksheet in the Same Workbook

To select the range E5:J10 on another worksheet (Sheet5) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5:J10")

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Cell on Another Worksheet in the Same Workbook

To select cell F7 on another worksheet (Sheet3) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet3").Cells(7, 6)

   -or-
 

Application.Goto (ActiveWorkbook.Sheets("Sheet3").Range("F7"))

You can also activate the worksheet, and then use method 1 above to select the cell:

Sheets("Sheet3").Activate
ActiveSheet.Cells(7, 6).Select

How to Select a Cell on a Worksheet in a Different Workbook

To select cell D4 on a worksheet (Sheet2) in a different workbook (Book2.xlsx), you can use either of the following examples:

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Cells(4, 4)

    -or-

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Range("D4")

You can also activate the worksheet, and then use method 1 above to select the cell:

Workbooks("Book2.xlsx").Sheets("Sheet2").Activate
ActiveSheet.Cells(4, 4).Select

How to Select a Range of Cells on the Active Worksheet

To select the range B2:D4 on the active worksheet, you can use any of the following examples:

ActiveSheet.Range(Cells(2, 2), Cells(4, 4)).Select

ActiveSheet.Range("B2:D4").Select

ActiveSheet.Range("B2", "D4").Select

How to Select a Cell on the Active Worksheet

To select cell C4 on the active worksheet, you can use either of the following examples:

ActiveSheet.Cells(4, 3).Select

-or-

ActiveSheet.Range("C4").Select

Automatic Birthday Mailer

This blog will help you send automatic mailer’s for birthdays, work anniversaries, marriage anniversaries, etc. Assuming almost everyone has Microsoft office in your systems, it is built on Microsoft Excel VBA platform.

These are the following options available on the tool:
1. It can send random customized messages for everyone on the list.
2. Attach a birthday Picture for everyone.
3. Set any Gmail/Yahoo/Office email id to send the mails.
4. Keep a bcc mail id to track if things are working fine.
5. Save the status of the last execution instance.

There is a batch file which opens the MS Excel file. Use the windows task scheduler for triggering the batch file on a particular time daily. Best is to keep the files on a server that is running all the time. And schedule the task on the server. Fill the details in the excel files as per your requirement.
Select pictures that you want to send as attachment.

Keep all the pictures in the Attachment folder, give all the file names a sequential ordering. It will be easier to identify in the excel file. Select images for similar type, i.e. same file extension, it’s easier to update later.

Select at least 20% unique messages. Add them to the MESSAGE sheet. Make sure to update the serial number in the MESSAGE sheet. Similar thing can be dome for customizing the subject lines.

Update the formula for the attachment and body according to your requirement.
The syntax would be:
=RANDBETWEEN(1,<number of unique attachments>)&".<FileExtension>"
=INDEX(Message!$B$2:$B$<row number of last item>,RANDBETWEEN(1,<number of unique messages>))

CODE MODIFICATION:
Open the Visual Basic Editor > Go to ThisWorkbook code section.
1. To send the mail from your laptop/Server configured outlook
    Call Module1.automated_email
2. To send the mail from gmail ID
    Call Module2.automated_email_gmail
3. To commit your execution instance
    ActiveWorkbook.Save
4. To close the workbook after execution
    ActiveWorkbook.Close

In the module2 update this two lines with your specific detail
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "SenderEmail@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "SenderEmailPassword"

Here the SenderEmail ID has to be a Gmail ID because the configuration in module 2 is set as per Gmail’s SMTP configuration,

If you want to send mail from Yahoo Mail the go to this link to get the SMTP configuration
http://warlocksolutions.blogspot.in/2017/08/automated-emails-from-excel-via-YahooMail.html

If you have your own Mail Server then best way is to use the Outlook method for sending. OR you can ask the server configuration details from your mail admin and update the same in the code.

IMPORTANT THING TO REMEMBER, FOLLOW STEP WISE:
1. The Attachments folder should be in the same directory where the BirthdayMailer file is placed.
2. After you update the file with new birthday details SAVEAS the file with current date.
3. In the code inside ThisWorkbook, Uncomment 1 OR 2 (Outlook or Gmail) as per your need. Also uncomment 3 AND 4 (Save and Close).
4. SAVEAS the file without current Date (BirthdayMailer.xlsm), after you close this file you cannot open this file anymore because the file will open, execute the code, save and then auto close. Replace the file in the laptop/server with this file.
5. In order to update new details next time, use the file with the date in the filename and repeat from step 1

HOW TO SET UP THE TRIGGER:
1. Right click on the batch file (trigger.bat) > Edit, Update the code and save it:
START EXCEL.EXE "<YOUR DESIRED LOCATION>\BirthdayMailer.xlsm"
2. Go to windows Task Scheduler and create a Basic Task:
3. Give a name to the Task. Ex: Birthday
4. Trigger Daily, Set the time as per your requirement.
5. Action > Start a program
6. Program/script: Browse the trigger.bat file
7. Select “Open the properties dialog for this task…” before you click finish.
8. In the properties window, update the run settings as per your requirement and save.
I would suggest keep all the files in one folder in the server.
1. BirthdayMailer.xlsm
2. trigger.bat
3. Attachments Folder

For any other queries please use the comments section below.

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

Selection of a specific sheet from a workbook

Hello Everyone,

The use case is as follows:
Data file which contains separate sheets for separate data. You need to select one sheet from all the available sheets in the file. 

In this example, 
1. User is asked to browse the data file
2. An userform shows a list of all the available sheets in the file
3. You select the required sheet and the data will be copied to your Tool workbook.

This may not be useful alone but you can combine this to your existing automation's where you are required to copy paste a data from a specific sheet in a file.

CODE in the module
===============
Sub fileOpen()
    toolWorkbook = ActiveWorkbook.Name
    On Error Resume Next
    data.Select
    data.Cells.Clear
    data.Range("A1").Select
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename(Title:="Please select an Excel file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Status"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If
    dataWorkbook = ActiveWorkbook.Name
    frm_sheetSelect.Show
End Sub

Sub copyData()
    Dim tempName As String
    Workbooks(dataWorkbook).Activate
    ActiveSheet.Select
    
    tempName = ActiveSheet.Name
    
    Cells.Select
    Selection.Copy
    Workbooks(toolWorkbook).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ActiveSheet.Name = tempName
    
    Workbooks(dataWorkbook).Activate
    ActiveWorkbook.Close False
    MsgBox "Data Import Complete", vbInformation, "Status"
End Sub

CODE in the userform

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

Private Sub UserForm_Activate()

    Dim sh As Worksheet

    Workbooks(Module1.dataWorkbook).Activate

    For Each sh In ActiveWorkbook.Sheets

        frm_sheetSelect.lb_sheetNames.AddItem sh.Name

    Next sh

End Sub

Private Sub cmd_select_Click()

    If lb_sheetNames.Value <> "" Then

        Sheets(lb_sheetNames.Value).Activate

    Else

        MsgBox "Please select the sheet (containing the raw data) from the list to continue.", vbExclamation, "Status"

        Exit Sub

    End If

    Unload Me

    Call Module1.copyData

End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

LINK FOR THE DATA FILE