spaces betwwen names in combo listClosed

Question

Hello, 

I have a list of employees, in and out dates as the status (active or inactive), my idea is to show up the employees with the active status records in a list using data validation, besides that Iam getting spaces between the employee names in the combo list because not all the employee are active right now so the list is bringing spaces and names.

This is my file if it helps.  https://authentification.site/files/23058853/Book1.xlsx

Thank you. i really dont know how to do it...

Mary


Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

i dont think it can be done like that. you would need a macro or sort data.

Mary · Answer

Hi, Do you have an idea how can I do that for my sheet.

I really would appreciate any help you can provide me

rizvisa1 · Answer

Try this

1. Open book
2. Press ALT + F11 to get into VBE
3. Press CTRL + R to launch Project Explorer
4. Double click on sheet where you have this data
5. Paste code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long

    On Error GoTo Error_Handler

    If (Intersect(Target, Range("C:E")) Is Nothing) Then GoTo End_Sub
    
    Application.EnableEvents = False

    Range(Cells(2, "L"), Cells(Rows.Count, "L").End(xlUp)).Clear
    
   
    
    For lRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
    
        If (Cells(lRow, "F") = "ACTIVE") Then
            
            Cells(Rows.Count, "L").End(xlUp).Offset(1, 0) = Cells(lRow, "C")
            
        End If
        
      Next lRow
      
End_Sub:

    Application.EnableEvents = True
    Exit Sub
    

Error_Handler:
    MsgBox Err.Description
    GoTo End_Sub
    
End Sub

Spaces betwwen names in combo list

3 responses

Similar discussions

Subscribe To Our Newsletter!