VBA with excel help please!?

Question

The prompt was to create a program that deletes duplicate entries in a range, and then display those remaining entries in a listbox in a userform. (and also show the numeric value for how many entries were deleted).

My problem is that I cant seem to populate the listbox or or the label.

Here is my module code:
Sub Delete()

Dim x As Long
Dim LastRow As Long

LastRow = 106

For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
Dim countUnique As Integer
countUnique = Application.CountA(Range("A:A"))

UserForm1.Show

End Sub
-----------------------------------
and my userform code:

Private Sub Label1_initialize()
'userform label that displays the amount of entries deleted
Label1.Caption = countUnique

End Sub

Private Sub CmndOk_Click()
Me.Hide

End Sub

Private Sub ListBox1_initialize()
'populates the listbox with the remaining entries
Dim row As Long
For row = 1 To 106
UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(row, 1)
Next row

End Sub

any suggestions guys/gals?

Answer 1

UserForms are Initialized, not Labels and Listboxes. I would also create the entire process when the UserForm is displayed.



Try something like:



In a standard module, copy and paste:



Sub shwForm ()

UserForm1.Show

End Sub



Create a keyboard shortcut to call the show form macro, or attach it to a command button on the sheet.



Then, in Userform1's code module copy and paste this:



Private Sub UserForm_Initialize()

Dim x As Long, row As Long

Dim LastRow As Long

Dim countUnique As Integer



LastRow = 106



For x = LastRow To 1 Step -1

If Application.CountIf(Range("A:A"), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x



countUnique = Application.CountA(Range("A:A"))



UserForm1.Label1.Caption = countUnique



For row = 1 To 106

UserForm1.ListBox1. AddItem Sheets("Sheet1").Cells(row, 1)

Next row



End Sub





Note: you can also use the reserved word 'Me' when referring to controls on a userform, instead of having to key the entire user form name each time. So Userform1.ListBox1 and Me.ListBox1 both refer to the same object. Also, you can abbreviate Application.WorksheetFunction.CountIf to simply Application.CountIf



These shortcuts save a lot of keying when you have a multitude of userforms and worksheet functions coded in VBA.



Edit: it comes to mind that you did not state whether the 106 in your code represents the column of entries before or after the routine has been called. It would actually be better to adapt the code to account for 'variable' entries. Otherwise, you will be adding 'blank' entries in your listbox, if your beginning row count is 106.



This would be a better solution:



Private Sub UserForm_Initialize()

Dim x As Long, row As Long

Dim LastRow

Dim countUnique As Integer



LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row

For x = LastRow To 1 Step -1

If Application.CountIf(Range("A:A"), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x



countUnique = Application.CountA(Range("A:A"))



UserForm1.Label1.Caption = countUnique



LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row



For row = 1 To LastRow

UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(row, 1)

Next row



End Sub

Answer 2

Probably the easiest way to go about what you are trying to do is to use a Collection. You can use a Key with a collection to make sure that only unique items are added to the collection. This will eliminate your duplicates without having to do a ton of extra programming to figure out which are duplicates.



As you create that unique collection, you create an array for the unique items. Then you can set your ListBox's List property equal to that array. This way you don't have to add the items in the listbox item by item. Sometimes if you do it item by items what can happen if you aren't careful is that you might get double and triple lists in your listbox because the macro may add items to the listbox every time you run it.



If you pull up the Object Browser (F2) then look up under the MSForms Library and and the ListBox Class for the List property. Select it and press F1 to find help on it or click the questionmark icon. Then pull up the example for the list property and it will show you how to populate the listbox very easily even for multiple columns in a listbox.



For help on Collections, look online some and use help on the Add Method for the Collection Class in the Object Browser. It has an example and will show you how the Key works.

Answer 3

you may convert pdf to excel format, however the macro VBA won't artwork interior the converted excel. only use a pdf to excel converter, google it, you will locate many, in my opinion utilising simpo pdf to excel, you aspects it a try.