Hi i need ot add to this code i have. Right now its if "office" then dont clear rows i need to add on to this thanks

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,137
Office Version
  1. 365
Platform
  1. Windows
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.



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:
Sort by date Sort by votes
zone709 said:
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.



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
Click to expand...
Hi zone709,

If I have understood correctly, then how about these changes (highlighted in red)?

Rich (BB 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 30
        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 = ""
                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
 
Upvote 0
Hi so the yard thing works fine thanks a lot for that addition, but when I change the rows from 4 to 30 right. it clears all those row cells in those rows which it should leave the data like office and yard does. Im not so good at this yet, but I think we need to leave
Code: 
For SRow = 4 To ERow
and then add to this
Code: 
If VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "office" And VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "yard" Then
what I wrote below

Rows 4 to 30 act the same as office and yard. I need rows 4 to 30 also to act like if office or yard don't clear the rows in the range above thanks.
 
Last edited:
Upvote 0
zone709 said:
Hi so the yard thing works fine thanks a lot for that addition, but when I change the rows from 4 to 30 right. it clears all those row cells in those rows which it should leave the data like office and yard does. Im not so good at this yet, but I think we need to leave
Code: 
For SRow = 4 To ERow
and then add to this
Code: 
If VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "office" And VBA.LCase(wsMF.Cells(SRow, 3).Text) <> "yard" Then
what I wrote below

Rows 4 to 30 act the same as office and yard. I need rows 4 to 30 also to act like if office or yard don't clear the rows in the range above thanks.
Click to expand...
So rows 4 to 30 should be kept no matter what? If so, try this:

Rich (BB 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 = 31 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 = ""
                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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
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"?
Replies
3
Views
427
Eric W
Eric W
Z
  • Question Question
I am running this long code and need a small additional coding in it.
Replies
1
Views
353
zone709
Z
Z
  • Question Question
I am trying to or would like to add code into this so it also highlights any changes in Col C
Replies
0
Views
316
zone709
Z

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top