Hi can anyone help me update this code i have. I cant figure out where or what to change to not delete any value in Column "D"?

[zone709]

I use this code to clear data off my sheet called "data" but I need now not to clear any value in Column "D" on this sheet. I'm not sure what to change in here cause I don't see any indication for Col "D" Maybe its Srow 4. Since column D is the 4th column. I don't know for sure why I'm asking for help. I think this is a real easy fix I hope so.

Private Sub btnClearData_Click()
    Dim wbmf As Workbook
    Dim wsMF, WsTool As Worksheet
    Dim wsUMF, WsTable As Worksheet
    Dim ERow, SRow As Integer
    Set wbmf = Workbooks("Master Time Sheet.xlsm")
    Set WsTool = wbmf.Sheets("Tool")
    Set wsMF = wbmf.Sheets("Data")
    Set wsUMF = wbmf.Sheets("UnMatchData")
    Set WsTable = wbmf.Sheets("MappingTable")
    WsTable.Range("F1").Value = ""
    WsTable.Range("G1").Value = ""
    Dim JobFields As String
    JobFields = "B3,B4"
    If JobFields <> "False" Then
        If VBA.InStr(1, JobFields, ",") > 0 Then
            WsTable.Range("F1").Value = VBA.Mid(JobFields, 1, VBA.InStr(1, JobFields, ",") - 1)
            WsTable.Range("G1").Value = VBA.Mid(JobFields, VBA.InStr(1, JobFields, ",") + 1, VBA.Len(JobFields))
        Else
            WsTable.Range("F1").Value = JobFields
            WsTable.Range("G1").Value = JobFields
        End If
    End If
    
    ERow = WsTool.Range("B" & WsTool.Rows.count).End(xlUp).Row
    WsTool.Range("A15:I" & ERow + 1).Value = ""
    ERow = wsMF.Range("A" & wsMF.Rows.count).End(xlUp).Row
    For SRow = 4 To ERow
        If VBA.IsNumeric(wsMF.Cells(SRow, 1).Text) Then
            If VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "office" And VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "yard" Then
                wsMF.Range("C" & SRow & ":G" & SRow).Value = ""
                wsMF.Range("I" & SRow & ":K" & SRow).Value = ""
                wsMF.Range("M" & SRow & ":O" & SRow).Value = ""
                wsMF.Range("Q" & SRow & ":S" & SRow).Value = ""
                wsMF.Range("U" & SRow & ":W" & SRow).Value = ""
                wsMF.Range("Y" & SRow & ":Z" & SRow).Value = ""
                wsMF.Range("AB" & SRow & ":AC" & SRow).Value = ""
                wsMF.Range("AH" & SRow).Value = ""
                If (Trim(wsMF.Range("H" & SRow).Text) <> Trim(WsTable.Range("F1").Text)) And (Trim(wsMF.Range("H" & SRow).Text) <> Trim(WsTable.Range("G1").Text)) Then
                    wsMF.Range("H" & SRow).Value = ""
                    wsMF.Range("L" & SRow).Value = ""
                    wsMF.Range("P" & SRow).Value = ""
                    wsMF.Range("T" & SRow).Value = ""
                    wsMF.Range("X" & SRow).Value = ""
                    wsMF.Range("AA" & SRow).Value = ""
                    wsMF.Range("AD" & SRow).Value = ""
                End If
                wsMF.Range("A" & SRow & ":AH" & SRow).Interior.Color = RGB(255, 255, 255)
            End If
        End If
    Next
    ERow = wsUMF.Range("E" & wsUMF.Rows.count).End(xlUp).Row
    wsUMF.Range("A4:AD" & ERow + 1).EntireRow.Delete
    WsTable.Range("F1").Value = ""
    WsTable.Range("G1").Value = ""
    Set wbmf = Nothing
    Set wsMF = Nothing
    Set wsUMF = Nothing
    
    MsgBox ("CLEARED")
End Sub

 

[Eric W]

This line:

wsMF.Range("C" & SRow & ":G" & SRow).Value = ""

clears out anything from column C to G in the given row (SRow), including column D. Change it to:

wsMF.Range("C" & SRow).Value = ""

wsMF.Range("E" & SRow & ":G" & SRow).Value = ""

 

[zone709]

Eric thank you. It works Happy Holidays

 

[Eric W]

You're welcome!

Happy holidays to you too! :wink: