0

i have a problem with my vba code. i have to generate a line and point numbers for my survey and grab a coordinate for that points from another txt file. It work fine until row number 1020, after that point, formula can't find a first empty cell in column, and insted continue to write data to first column.

my formula to find a first empty cell in column is

Worksheets("POSTAVLJANJE").Activate
        NextFree = Range("B3:B" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
        Range("B" & NextFree).Select

any idea?

Ilija Vasiljević
  • 3
  • 1
  • xlCellTypeBlanks returns all the blank cells, not just one. – vacip May 12 '17 at 07:56
  • Maybe this can help: http://stackoverflow.com/questions/27065840/meaning-of-cells-rows-count-a-endxlup-row/27066381#27066381 – vacip May 12 '17 at 07:57
  • SpecialCells(xlCellTypeBlanks) will ignore the empty cells after the last used cell in column B. Are you sure you have an empty cell in column B before the last row with data in column B? – Subodh Tiwari sktneer May 12 '17 at 08:00
  • Possible duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) – vacip May 12 '17 at 08:00

1 Answers1

1

Try using this instead:

NextFree = Range("B" & Rows.Count).End(xlUp).Row+1

This will find the first non empty row starting from the bottom, and add one to indicate the first row below that.

vacip
  • 5,246
  • 2
  • 26
  • 54