One way to do this is to remove the ListItems as well as the ColumnHeaders, and populate the ListView again.
For your specific case, just replace the first two procedures in your userform module
Code:'insert records in worksheet Private Sub CommandButton1_Click() Dim r As Long Dim m As Long Dim rngList As Range If Me.TextBox1 = "" Or Me.TextBox2 = "" Or Me.TextBox3 = "" Then MsgBox "Please enter All records", vbExclamation Exit Sub End If Set rngList = Worksheets("Sheet1").Range("Listm") m = rngList.Rows.Count rngList.Cells(m + 1, 2) = Me.TextBox1 rngList.Cells(m + 1, 3) = Me.TextBox2 rngList.Cells(m + 1, 4) = Me.TextBox3 'rngList.Cells(m + 1, 5) = Me.TextBox4 'Unload Me Sheet1.Cells(1, 1).Select Sheet1.Cells(2, 1).Select LoadListView End Sub Private Sub LoadListView() 'Declare the variables Dim wksSource As Worksheet Dim rngData As Range Dim rngCell As Range Dim LstItem As ListItem Dim RowCount As Long Dim ColCount As Long Dim i As Long Dim j As Long 'Set the source worksheet Me.ListView1.ListItems.Clear Me.ListView1.ColumnHeaders.Clear Set wksSource = Worksheets("Sheet1") 'Set the source range Set rngData = wksSource.Range("A1").CurrentRegion 'Add the column headers For Each rngCell In rngData.Rows(1).Cells Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90 Next rngCell 'Count the number of rows in the source range RowCount = rngData.Rows.Count 'Count the number of columns in the source range ColCount = rngData.Columns.Count 'Fill the ListView For i = 2 To RowCount Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value) For j = 2 To ColCount LstItem.ListSubItems.Add Text:=rngData(i, j).Value Next j Next i End Sub




Reply With Quote
Bookmarks