Tweeking Next Blank Row Code Please :)

[symonk]

Hi Folks, I currently have a spreadsheet with a user interface recording alot of data, currently my method of recording each save to the next row is working alright (its usable but not ideal).

My problem is this, if I leave the first Textbox empty on any entry, the next entries will not jump to the next line, they will overwrite the last row indefinately until the first textbox has something populating it.

I'm not very experienced with the coding side of this, so I appreciate any help you can give me.

Example of the Save to worksheet code (Cmdbutton)

   Dim yRow As Long
    Dim ws As Worksheet
    Set ws = Sheet5
     'Prompt user before adding record
    MsgBox "Are you sure you want to Add record?", vbOKOnly, Verify
     ' Find emtpy row
    yRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     'Add data to worksheet
    
    ws.Cells(yRow, "A") = TextBox68.Value
    ws.Cells(yRow, "B") = TextBox69.Value
    ws.Cells(yRow, "C") = TextBox70.Value
    ws.Cells(yRow, "D") = TextBox71.Value
    ws.Cells(yRow, "E") = TextBox72.Value
    ws.Cells(yRow, "F") = TextBox73.Value
    ws.Cells(yRow, "G") = TextBox74.Value
    ws.Cells(yRow, "H") = TextBox75.Value
    ws.Cells(yRow, "I") = TextBox76.Value
    ws.Cells(yRow, "J") = TextBox77.Value
    ws.Cells(yRow, "K") = TextBox78.Value
    ws.Cells(yRow, "L") = CheckBox9.Value
    ws.Cells(yRow, "M") = TextBox79.Value
    ws.Cells(yRow, "N") = TextBox80.Value

It is fit for purpose right now as long as the first textbox is populated, in this example (TextBox68), however with a blank TextBox68 it will not jump to the next blank line until it is filled, just constantly overwriting this particular row indefinately.

Thanks alot for your time & expertise :D

 

[xenou]

Hi,
Adjust to:

   [COLOR="Navy"]Dim[/COLOR] yRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
    [COLOR="Navy"]Set[/COLOR] ws = Sheet5
     [COLOR="SeaGreen"]'Prompt user before adding record[/COLOR]
    MsgBox "Are you sure you want to Add record?", vbOKOnly, Verify
     [COLOR="SeaGreen"]' Find emtpy row[/COLOR]
    [B]yRow = GetLastRow(ws) + 1[/B]
     [COLOR="SeaGreen"]'Add data to worksheet[/COLOR]
    
    ws.Cells(yRow, "A") = TextBox68.Value
    ws.Cells(yRow, "B") = TextBox69.Value

Where you include this function in the same module or sheet as your original sub:

[COLOR="Navy"]Function[/COLOR] GetLastRow(ByRef ws [COLOR="Navy"]As[/COLOR] Worksheet) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="SeaGreen"]'Returns number of last used row on a worksheet[/COLOR]
    [COLOR="Navy"]If[/COLOR] WorksheetFunction.CountA(ws.Cells) = 0 [COLOR="Navy"]Then[/COLOR]
        GetLastRow = 1
    [COLOR="Navy"]Else[/COLOR]
        GetLastRow = ws.Cells.Find("*", [A1], xlFormulas, xlPart, _
            xlByRows, xlPrevious, False, False).Row
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

This way you get the last row that is filled in, not just the last row in column 1 that is filled in.

 

[symonk]

will give that a go in work tomorrow Xenou,

Thanks very much for your help. appreciate it