Finding the First Empty Cell in a Column

A common task when using WinWedge to feed data to an Excel spreadsheet using Dynamic Data Exchange (DDE) is to stack data received from WinWedge in a column in a worksheet where each new data reading is always stored at the bottom of a column in the worksheet. The problem is how to find the first empty cell at the bottom of a column in the most efficient way.

There are a number of ways to accomplish this however the most efficient way that we have found so far is using the technique in the following Excel VBA function. The following subroutine accepts either a sheet name (or the ordinal number for a sheet) and a column number as input variables and then returns the row number of the first empty cell at the bottom of the specified column.

 Function FindBottomRow(WhatSheet As Variant, WhatColumn As Long) As Long
   Dim R As Long
   R = Sheets(WhatSheet).Cells(65534, WhatColumn).End(xlUp).Row
   If Len(Sheets(WhatSheet).Cells(R, WhatColumn).Text) Then R = R + 1
   FindBottomRow = R
End Function

Contact Us