title: Find row indices of empty cells in a given range
body: <p>Is it possible to write a vba macro that determines if there are any empty cells in a given range and returns the row number of that cell? </p>
  
  <p>I'm new to vba and all that I managed to write after searching the internet was something that takes a range and colors every emty cell in it red:</p>
  
  <pre><code>Sub EmptyRed()
      If TypeName(Selection) &lt;&gt; "Range" Then Exit Sub
      For Each cell In Selection
          If IsEmpty(cell.Value) Then cell.Interior.Color = RGB(255, 0, 0)
      Next cell
  End Sub 
  </code></pre>
  
  <p>The macro does basically what I want, but instead of coloring the empty cell red I would like to know the row index of the empty cell. </p>
  
  <p>A little background info: I have a very large file (about 80 000 rows) that contains many merged cells. I want to import it into R with readxl. Readxl splits merged cells, puts the value in the first split cell and NA into all others. But a completely empty cell would also be assigned NA, so I thought the best thing would be to find out which cells are empty with Excel, so that I know which NA indicate a merged cell or an empty cell. Any suggestions on how to solve this problem are very welcome, thanks!</p>
  
  <p>Edit: To clarify: Ideally, I want to unmerge all cells in my document and fill each split cell with the content of the previously merged cell. But I found macros on the web that are supposed to do exactly that, but they didn't work on my file, so I thought I could just determine blank cells and then work on them in R. I usually don't work with Excel so I know very little about it, so sorry if my thought process is far too complicated.</p>