0

I'm trying to find the number of the first empty row in my sheet in the excel via vbscript. Can some one help me out how to do that? that's what I did:

With objSheet
    iRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
msgbox iRow

It's not working well if the sheet is empty. Thanks!

Shemesh
  • 39
  • 11

4 Answers4

0

You can check if, by doing xlDown, if you arrive at row 1048576 (that's the last row on the sheet).

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • In your case, when the row is empty, the value of `iRow` is 1048576, which is an indication that you arrive at the last row of your sheet. – Dominique Oct 08 '18 at 11:53
  • So what should I change? – Shemesh Oct 08 '18 at 11:54
  • What do you want to see in the message box in case of an empty row? – Dominique Oct 08 '18 at 11:55
  • The number of the first row that is empty from the beginning. such as - A:1 - A:3 is not empty than the iRow value is 4. – Shemesh Oct 08 '18 at 11:56
  • Yes, and your code is correct in case the sheet is not empty. In case your sheet is empty, you will see 1048576, which you don't want. What do you want to see instead of 1048576? – Dominique Oct 08 '18 at 11:59
  • When the sheet is empty it's working good and it adds. The second time the iRow value its 1 but it should be 2 because the first row is not empty. – Shemesh Oct 08 '18 at 12:03
  • Ah, now I understand your problem: you should simply add 1 to your result: the button `xlDown` goes to the last non-empty cell of your row. If you want to get the first empty cell instead, you just need to go one step further, or add 1 to your `iRow` result. – Dominique Oct 08 '18 at 12:05
0

Your now modified (with xlup) script will return the last NON-empty row in the column; unless the column is empty in which case it will return a 1. So if you want the first empty row, you need to add 1.

unless the script returns a 1. In that case, you need to check A1 to see if it is empty or not, and only add 1 if it is.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

the answer as u said its:

With objSheet
    If objSheet.Cells(1,1).value = "" Then
        iRow = 1
    else
        iRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End If
End With

thanks guys

Shemesh
  • 39
  • 11
  • This code will return the LAST empty cell in a column, not the FIRST one, as you said in your question. Please, edit the title of your question, because it may confuse future users looking for help – Foxfire And Burns And Burns Oct 08 '18 at 12:22
0

To get the FIRST empty cell in a column, you can use:

With objSheet
    If .Range("A1") = "" Then
        iRow = 1
    ElseIf .Range("A2") = "" Then
        iRow = 2
    Else
        iRow = .Range("A1").End(xlDown).Offset(1, 0).Row
    End If
End With

You must first check if first row or second row are empty, because End(xlDown) behaves different if row 1 is non-empty and second row is empty, it can skip it and returns the wrong value.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38