Hi I need to add on this code. It works fine and clears what I need, but I need to add 2 things. I would like to add yard also if office or yard and also number lines 4 to 30. Not to clear it thanks
1. If VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "office" Then <----- or "yard" and rows 4 to 30. I need to add this to the left. Just these additions. Thanks for the help in advance.
1. If VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "office" Then <----- or "yard" and rows 4 to 30. I need to add this to the left. Just these additions. Thanks for the help in advance.
Code:
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" 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 = ""
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
Last edited: