1
lastrow = ThisWorkbook.Sheets("Numbers").Cells(Rows.Count, 1).End(xlUp).Row

I have data until the 55th row but last row gives me 70 as the formula is dragged till the 70th row and rows from 55 to 70 contain a blank space as per the formula. How can I avoid this?

Meesha
  • 801
  • 1
  • 9
  • 27

1 Answers1

2

Loop around all cells in range then work out the first non-blank cell. Set lastrow to that cell

lastrow = ThisWorkbook.Sheets("Numbers").Cells(Rows.Count, 1).End(xlUp).Row

for i = lastrow to 1 step -1
    if ThisWorkbook.Sheets("Numbers").Cells(i, 1) <> " " then
        lastrow = i
        exit for
    end if
next
DiegoAndresJAY
  • 706
  • 4
  • 11
99moorem
  • 1,955
  • 1
  • 15
  • 27
  • 2
    Once lastrow is given the value of i, you are going to want to have it exit the for loop. If you don't exit the loop, it will keep going and eventually give lastrow a value of 1. – rwilson Jul 30 '15 at 15:47
  • @rwilson I figured thast out when I got 1 on the first attempt. Thanks. – Meesha Jul 30 '15 at 16:06