Excel VBA code tweaking

[TSO456]

I use the code below to send emails from excel. I am trying to enter code on the LastRow line so excel will go to the last cell in the sheet instead of me having to specify the lastrow in the spread sheet that contains data.
Thank you
Jill

Set Sht = Worksheets("TEST")

FirstRow = 2
LastRow = 250 ' Eventually I will Insert code here to find the last row in the clients list
For x = FirstRow To LastRow
SendTo = Sht.Range("B" & x).Value
If Len(SendTo) > 0 Then
Esubject = Sht.Range("C" & x).Value

 

[Loomah]

Hi
Have a look in the FAQ section as there are at least 2 excellent(!!) FAQs on the subject of finding the last row etc

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[byundt]

Jill,
Try something like:

LastRow=Sht.UsedRange + Sht.UsedRange.Rows.Count -1

The first reference to UsedRange should return the first line used (possibly row 2 in your example), while the last references counts the number of rows in the used range. You need to subtract 1 from the sum of those two numbers to get the last row number.

As a free benefit, this instruction will reset the vertical scrollbar in case it has gotten out of whack due to data being inserted way down the worksheet and then deleted.
Brad