Remove Numerical Values from Listbox and Certain Text Value

C

Corey

I need to remove ANY Value that is a NUMERIC VALUE and
Remove any Value that is a Text Value = "Section Length" within Column C in the Below Code,
so they are NOT listed.

How can i do this ?


Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 1).Offset(-1, 2) <> "" Then
On Error Resume Next
NoDupes.Add .Cells(myrow, 1).Offset(-1, 2).Value, CStr(.Cells(myrow, 1).Offset(-1,
2).Value)
If Err.Number = 0 Then
ListBox1.AddItem .Cells(myrow, 1).Offset(-1, 2)
End If
On Error GoTo 0
End If
' End If
Next
End With
Application.ScreenUpdating = True
End Sub

Corey....
 
J

JLGWhiz

Correction:
For myRow = 1 to lastCell

If Not IsNumeric(Cells(MyRow, 3)) And Cells(myRow, 3) <> "Section Length" Then

'Insert code

Next
 
C

Corey

Got it

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 1).Offset(-1, 2) <> "" Then
On Error Resume Next
NoDupes.Add .Cells(myrow, 1).Offset(-1, 2).Value, CStr(.Cells(myrow, 1).Offset(-1,
2).Value)
If Err.Number = 0 Then
If .Cells(myrow, 1).Offset(-1, 2).Value <> "" And .Cells(myrow, 1).Value <> "" Then
ListBox1.AddItem .Cells(myrow, 1).Offset(-1, 2)
End If
End If
On Error GoTo 0
End If
' End If
Next
End With
Application.ScreenUpdating = True
End Sub


I need to remove ANY Value that is a NUMERIC VALUE and
Remove any Value that is a Text Value = "Section Length" within Column C in the Below Code,
so they are NOT listed.

How can i do this ?


Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 1).Offset(-1, 2) <> "" Then
On Error Resume Next
NoDupes.Add .Cells(myrow, 1).Offset(-1, 2).Value, CStr(.Cells(myrow, 1).Offset(-1,
2).Value)
If Err.Number = 0 Then
ListBox1.AddItem .Cells(myrow, 1).Offset(-1, 2)
End If
On Error GoTo 0
End If
' End If
Next
End With
Application.ScreenUpdating = True
End Sub

Corey....
 
J

JLGWhiz

This would be my approach:

If Not IsNumeric(Cells(MyRow, 3)) And Cells(myRow, 3) <> "Section Length" Then

'Insert code For..Next Loop

End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top