Remove Numerical Values from Listbox and Certain Text Value

  • Thread starter Thread starter Corey
  • Start date Start date
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....
 
Correction:
For myRow = 1 to lastCell

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

'Insert code

Next
 
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....
 
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
 
Back
Top