Excel 365. I have a small range “noteList” of 1 column (L) and 30 rows (13-43) on sheet “Home”. I created a userform to add and clear contents of rows (to facilitate doing this from any sheet in the workbook). When user clears a row of data it leaves an empty row so my data list may have one or several empty rows. When adding a new record to the range it is always added to the first empty row at the bottom of the range (.Cells(.Rows.Count, “L”).End(xlUp).Row + 1). I am trying to find a way for vba when adding new data to either choose the first available empty row from the top; or after adding the new row contents at the bottom re-organize/consolidate the range to move data up as needed to fill any empty rows. I cannot add/delete rows because there is other data on the sheet, I can only add/clear contents.

My code thus far:

Private Sub UserForm_Initialize()

Me.StartUpPosition = 0
Me.Top = Application.Top + 100
Me.Left = Application.Left + Application.Width – Me.Width – 575
ComboBox1.List = Worksheets(“Home”).Range(“L13:L43”).Value
End Sub
Private Sub CommandButton1_Click()
Dim noteList As Range, i As Range

Set noteList = Range(“L13:L43”)

Set i = noteList.Find(Me.ComboBox1.Value, LookIn:=xlValues)

i.Offset.ClearContents

Unload Me

End Sub
Private Sub CommandButton3_Click()

Dim lngR As Long

With Worksheets(“Home”)

lngR = .Cells(.Rows.Count, “L”).End(xlUp).Row + 1

.Range(“L” & lngR).Value = Me.TextBox2.Text

End With

Unload Me

End Sub
Private Sub CommandButton2_Click()
Unload Me

End Sub

I am still new to vba and cannot figure this out.

Sorry if this question is redundant, I already posted one similar but got no responses and could not find the post again so I don’t know what happened to it.

​Excel 365. I have a small range “noteList” of 1 column (L) and 30 rows (13-43) on sheet “Home”. I created a userform to add and clear contents of rows (to facilitate doing this from any sheet in the workbook). When user clears a row of data it leaves an empty row so my data list may have one or several empty rows. When adding a new record to the range it is always added to the first empty row at the bottom of the range (.Cells(.Rows.Count, “L”).End(xlUp).Row + 1). I am trying to find a way for vba when adding new data to either choose the first available empty row from the top; or after adding the new row contents at the bottom re-organize/consolidate the range to move data up as needed to fill any empty rows. I cannot add/delete rows because there is other data on the sheet, I can only add/clear contents.My code thus far:Private Sub UserForm_Initialize()Me.StartUpPosition = 0Me.Top = Application.Top + 100Me.Left = Application.Left + Application.Width – Me.Width – 575ComboBox1.List = Worksheets(“Home”).Range(“L13:L43”).ValueEnd SubPrivate Sub CommandButton1_Click()Dim noteList As Range, i As RangeSet noteList = Range(“L13:L43”)Set i = noteList.Find(Me.ComboBox1.Value, LookIn:=xlValues)i.Offset.ClearContentsUnload MeEnd SubPrivate Sub CommandButton3_Click()Dim lngR As LongWith Worksheets(“Home”)lngR = .Cells(.Rows.Count, “L”).End(xlUp).Row + 1.Range(“L” & lngR).Value = Me.TextBox2.TextEnd WithUnload MeEnd SubPrivate Sub CommandButton2_Click()Unload MeEnd Sub I am still new to vba and cannot figure this out.Sorry if this question is redundant, I already posted one similar but got no responses and could not find the post again so I don’t know what happened to it.  Read More

​