Deleting part of a column without deleting whole rows.

[dstrdOne]

I have an Automated report...I have A Name in A3 and a decimal time in B3 and that run as long as 300 names. SO list one is in A:B, List 2 is C:D, and List 3 is E:F, Each list has a different set of valid info...

For List one

Column B>11 is valid and should be kept <11 is a trash the for that row A:B should be cleared,
Column D>31is valid and should be kept <31is a trash and for that row C:D should be cleared,
Column f>11 is valid and should be kept <11 is a trash the for that row E:F should be cleared,

I can't delete since I cannot disrupt the other 2 lists. My Columns will be drastically uneven but it is what I need.

(The typical length of the Idol None list with the applied validation is usually about 165 agents, Th Idol Break is usually 70 agents, and Idle_system is usually less than 5 but since there are 300 seats I copied my formula to Row 300)

I have been lurking here and googling the code to do this but only find code that will delete the entire row. Is there a Macro for me?

Thanks in Advance!

[patel45]

Sorry, I did not understand, attach a new file with current and desired table, highlight the cells to be cleared

[OllieB]

something like

Public Sub RemoveInvalidListEntries()
     '#
     '# declare private variables
     '#
          Dim lngListRow As Long
          Dim lngTargetRow As Long
     '#
     '# for the active worksheet
     '#
          With ActiveSheet
          '#
          '# list 1: remove invalid entries
          '#
               lngTargetRow = 1
               For lngListRow = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
                    If Val(.Cells(lngListRow, "B").Value) >= 11 Then
                         lngTargetRow = lngTargetRow + 1
                         If lngTargetRow <> lngListRow Then
                              .Cells(lngTargetRow, "A").Value = .Cells(lngListRow, "A").Value
                              .Cells(lngTargetRow, "B").Value = .Cells(lngListRow, "B").Value
                         End If
                    Else
                         .Cells(lngListRow, "A").Value = vbNullString
                         .Cells(lngListRow, "B").Value = vbNullString
                    End If
               Next lngListRow
          '#
          '# list 2: remove invalid entries
          '#
               lngTargetRow = 1
               For lngListRow = 2 To .Cells(.Rows.Count, "D").End(xlUp).Row
                    If Val(.Cells(lngListRow, "D").Value) >= 31 Then
                         lngTargetRow = lngTargetRow + 1
                         If lngTargetRow <> lngListRow Then
                              .Cells(lngTargetRow, "C").Value = .Cells(lngListRow, "C").Value
                              .Cells(lngTargetRow, "D").Value = .Cells(lngListRow, "D").Value
                         End If
                    Else
                         .Cells(lngListRow, "C").Value = vbNullString
                         .Cells(lngListRow, "D").Value = vbNullString
                    End If
               Next lngListRow
          '#
          '# list 3: remove invalid entries
          '#
               lngTargetRow = 1
               For lngListRow = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
                    If Val(.Cells(lngListRow, "F").Value) >= 11 Then
                         lngTargetRow = lngTargetRow + 1
                         If lngTargetRow <> lngListRow Then
                              .Cells(lngTargetRow, "E").Value = .Cells(lngListRow, "E").Value
                              .Cells(lngTargetRow, "F").Value = .Cells(lngListRow, "F").Value
                         End If
                    Else
                         .Cells(lngListRow, "E").Value = vbNullString
                         .Cells(lngListRow, "F").Value = vbNullString
                    End If
               Next lngListRow
          End With
     End Sub

[dstrdOne]

Solved! OllieB, This was perfect and I thank you!

[JOHN H. DAVIS]

Maybe:

Sub dstrdOne()
Dim lr As Long
Dim rcell As Range

lr = ActiveSheet.UsedRange.Rows.Count

For Each rcell In Range("B2:B" & lr)

    If rcell.Value < 11 Then
    
        Range(rcell.Offset(, -1), rcell).ClearContents
        
    End If
    
Next rcell
For Each rcell In Range("D2:D" & lr)

    If rcell.Value < 31 Then
    
        Range(rcell.Offset(, -1), rcell).ClearContents
        
    End If
    
Next rcell
For Each rcell In Range("F2:F" & lr)

    If rcell.Value < 11 Then
    
        Range(rcell.Offset(, -1), rcell).ClearContents
        
    End If
    
Next rcell

End Sub