Questions? Search our site or call us at 1-800-722-6004

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

 


Categories: Excel Tips & Tricks, Microsoft Excel

Last Updated: 2011.07.06

Need more help?

Don't hesitate to call or email us with your questions

Our office is open 9AM - 5PM Monday Through Friday (E.S.T.)

Technical Support: 215-496-0202

Toll-Free: 1 (800) 722-6004
Skype: taltech1 (Voice only)
Email: support@TALtech.com