Find last name in column

Question

I am setting up some VBA to auto send emails but cannot get the range right. I need the range to stop at the last filled row in column B.

I have tried;

For Each cell In ws.Range("B2", Selection.End(xlDown))

For Each cell In ws.Range("B2", ("b" & Cells.Rows.Count).End(xlUp))

The second line doesn't work but the top line selects all cells in column B if there are only one or two emails in column B.

Code below;

Range("B3").Select

Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet

Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet
For Each cell In ws.Range("B2", Selection.End(xlDown))
  Set objMail = objOutlook.CreateItem(0)
  With objMail
    .To = cell.Value

I need the range to stop at the last filled row in column B no matter how many rows have data.

**[1]** Find the last row using `xlup` or `.Find`. You may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) **[2]** Construct your range and then loop through that range. — Siddharth Rout, Mar 25 '19 at 11:59

score 0 · Accepted Answer · answered Mar 25 '19 at 11:30

You've just got a syntax error. Try using

For Each cell In ws.Range("B2", ws.Range("B" & ws.Cells.Rows.Count).End(xlUp))

Using End(xlDown) will stop at the first empty cell it finds. This could be ok but if you have any gaps in your data it will miss anything after the first empty cell. So it is better to use End(xlUp) as this will find the first populated cell before the bottom of the column avoiding missing any of your data.

Find last name in column

1 Answers1