Searching for first Empty Row in a column - Microsoft Excel 1

[EliseFreedman]

I have a Visual Basic Program which connects to a spreadsheet and outputs the contents to a text file ready for updating some remote databases

The spreadsheet contains a list of products together with the date that they are due to come back into stock. Further down the same spreadsheet, there are details regarding discontinued products etc. However, the only part that I am interested in is the out of stock products.

My problem is that each week, the number of items which are out of stock will vary and therefore the number of rows to be output to the file will vary. At the moment, I am loading the spreadsheet manually, searching through it to find the last row of the out of stocks and changing the reference in my visual basic program to reflect this

I would like to change it so that my visual basic program searches for the first empty row and then determines the range to outout to the file automatically.

How would I do this. For reference, I have attached a sample spreadsheet and my vb program

A sample spreadsheet is as follows

Code Description ETA
BB-AG101 9" HEAVY DUTY ANGLE GRINDER 24-Nov-03
AG300 115 X 3MM STEEL CUTTING DISC 01-Jan-00
AG301 115 X 3MM STONE CUTTING DISC 06-Nov-03
BB-BD338 2'' X 20' RATCHET TIE DOWN 07-Nov-03


DISCONTINUED

BB-BS202 21" BOW SAW WITH HARDPOINT BLADE
BB-CL111 3PC CLAMP SET WITH STAND
BB-CS800 14" CUT OFF SAW
BB-CW102 5MX0.6MX50mm GALVANISED WIRE NETTING

The VB program is currently as follows

Dim upnum As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strcn As String
Dim strsql As String
Dim mousepointer As Integer
Screen.mousepointer = 11

'delete existing file
Kill "c:\order entry\oos.dat"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
strcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\order entry\oos.xls;" & "Extended Properties=""Excel 8.0;"""
cn.Open strcn

Open "c:\order entry\OOS.DAT" For Random As #3 Len = 60
upnum = 0
With rs
strsql = "Select * from [OofSReport$b1:d135]" ' change each week according to spreadsheet reference
.Open strsql, cn, adOpenDynamic, , adCmdText

If .BOF = False Then
While .EOF = False
upnum = upnum + 1
UPSOS.SRCODE = !Code
UPSOS.SRDESC = !Description
UPSOS.SRDATE = Format(!ETA, "dd/mm/yyyy&quot
Put 3, upnum, UPSOS
.MoveNext
Wend
End If
.Close
End With

Close #3

Set rs = Nothing
cn.Close
Screen.mousepointer = 1
End

 

[jcrater]

something like this:

dim iLastRow as integer

Cells(iStartRow, iColumn).Select
Selection.End(xlDown).Select
iLastRow = Selection.Row

Selects the row number of the first row starting from iStartRow that is empty.

 

[tbl]

Hi Elise,
This looks deceptively easy, but probably because I haven't understood it properly !
Does your problem come down to finding the first blank cell in an Excel column, or is it much more complex than that ?

Richard

 

[jcrater]

This accomplishes the same as using the [end + arrow] keys if you've used them. If your currently selected cell contains data and you use the xlDown method, the cell will stop on the next row that does NOT contain data (in the current column, of course). Likewise, if the currently selected cell does not contain data, the xlDown method will stop on the next row that DOES contain data.