VBA Next available row

user_13483253 0
Viewed 92

Can anyone help with the following. Working in excel. Have created a table that is filled using a form. Cells in range a:1 to J:31 (Table1). The form auto completes the table, however when an entry is deleted (anywhere) in the table I need the form to fill those empty spcaes. At the moment once the table is complete even when information I deleted no new entries can be entered using the form. In addition i need the form to fill the first empty cell.

Lets say that the only space available that I have is row 12, I want to be able to put the information there using my userform.

The table is an admissions to a hospital sheet including name, number, bedroom, date of arrival, doctor and other such info, filled across. The main point is that I don't want to create another row to enter data, the data should be just in the range of the table a:1 to J:31

I have this code in vba

Private Sub CommandButtonSave_Click()

Dim fill As Lonng
Sheets("Ward Planner").Activate

fill = WorksheetFunction.CountA(Range("Table1")) + 1

Cells(fill, 1).Value = ComboBoxBed
Cells(fill, 2).Value = TextBoxName.Text
Cells(fill, 3).Value = ComboBoxConsultant
Cells(fill, 4).Value = TextBoxPcn.Text
Cells(fill, 5).Value = TextBoxDoa.Text
Cells(fill, 6).Value = ComboBoxGender
Cells(fill, 7).Value = ComboBoxStatus
Cells(fill, 8).Value = ComboBoxDiet
Cells(fill, 9).Value = TextBoxComments.Text

End Sub
vba excel
2 Answers

It looks like you need a test to see whether a blank row is available or not - then some code to find that row (based on column A) before pasting your data to it. The following code is untested but should work given the description in your question. Please try it & let me know how it goes.

Private Sub CommandButtonSave_Click()

Dim c As Range, fill As Long, ws As Worksheet
Set ws = Sheets("Ward Planner")

If Application.WorksheetFunction.CountBlank(ws.Range("A1:A31")) = 0 Then
    MsgBox "No available rows"
    Exit Sub
End If

For Each c In ws.Range("A1:A31")
    If c.Value = "" Then
        fill = c.Row
        
        'ws.Cells(fill, 1).Value...etc" code goes here
        'copy your current code - but please note the "ws." prefix
        
        Exit Sub
    End If
Next c

End Sub
Admin1

I found that this code works too, just in case someone would like to try both.

Dim fill As Long
   Sheets("Ward_Planner").Activate
   On Error Resume Next
   fill = Range("A2:A29").SpecialCells(xlBlanks)(1).Row
   On Error GoTo 0
   If fill = 0 Then
    MsgBox "all beds are filled"
   Exit Sub
   End If
   Cells(fill, 1).Value = ComboBoxBed.Value
   Cells(fill, 2).Value = TextBoxName.Value***
user_134832530
Related