Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Clear contents based on contents in another sheet

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clear contents based on contents in another sheet

    Hello everyone. New VBA user here and in need of some help. I have a workbook with two sheets, “Max Data” and “Min Data”. Both sheets have the same number of columns with data in columns A and B being the same. Columns C and on have unique values.

    Part Number Serial Number Data Data
    1 XXXX-1 XXX XXX
    2 XXXX-2 XXX XXX
    3 XXXX-3 XXX XXX
    4 XXXX-4 XXX XXX
    5 XXXX-5 XXX XXX
    Part Number Serial Number Data Data
    1 XXXX-1 XXX XXX
    2 XXXX-2 XXX XXX
    3 XXXX-3 XXX XXX
    4 XXXX-4 XXX XXX
    5 XXXX-5 XXX XXX

    I have a macro that will clear contents on the “Max Data” sheet based on certain criteria. The result will look something like this:

    Part Number Serial Number Data Data
    1 XXXX-1 XXX XXX
    2 XXXX-3 XXX XXX
    3 XXXX-5 XXX XXX
    4
    5
    Part Number Serial Number Data Data
    1 XXXX-1 XXX XXX
    2 XXXX-2 XXX XXX
    3 XXXX-3 XXX XXX
    4 XXXX-4 XXX XXX
    5 XXXX-5 XXX XXX

    What I need is a macro that compares the values in Column B of both sheets and clears contents from the “Min Data” sheet if it’s not found on the “Min Data” sheet. Looking at the images above the value in B7 on the “Min Data” sheet is not found in column B on the “Max Data” sheet. I need to clear the contents from the “Min Data” sheet also. The calculations in the last row of both sheets is used elsewhere hence the need to clear contents rather than delete the entire row.

    Any help would be greatly appreciated!



    Edit: Reading this after posting it looks really confusing. Sorry about that.

  2. #2
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    bump

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,791
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by n3wpv View Post
    bump
    Your EDIT on the original post is accurate. Maybe if you provide sufficient detail, label sheet names and show a before and after comparison to indicate what you want to achieve, someone can help you.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by JoeMo View Post
    Your EDIT on the original post is accurate. Maybe if you provide sufficient detail, label sheet names and show a before and after comparison to indicate what you want to achieve, someone can help you.
    I tried to add screenshots but was unable to.

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,791
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by n3wpv View Post
    I tried to add screenshots but was unable to.
    Here are two links to alternative ways (both free) to post your sheet(s) layout(s) so those who want to help can easily copy the data to Excel and work with it.
    Download
    http://www.mrexcel.com/forum/2545970-post2.html
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    I have a workbook with two sheets, “Max Data” and “Min Data”. Both sheets have the same number of columns with data in columns A and B being the same. Columns C and on have unique values.

    Excel 2012
    A B C D
    1 Part Serial Number P R
    2
    3 1 D13-00-00-1 0.0168 0.0233
    4 2 D13-00-00-4 0.0256 0.0134
    5 3 D13-00-00-5 0.0296 0.0124
    6 4 D13-00-00-6 0.0281 0.0131
    7 5 D13-00-00-7 0.0225 0.0118
    8 6 D13-00-00-9 0.0294 0.0129
    9 7 D13-00-00-12 0.0308 0.0135
    10 8 D13-00-00-13 0.0302 0.0142
    11 9 D13-00-00-15 0.0288 0.0124
    12 10 D13-00-00-16 0.0291 0.0143
    13 11 D13-00-00-17 0.0199 0.0173
    14 12 D13-00-00-21 0.0268 0.0153
    15 13 D13-00-00-22 0.0189 0.0123
    16 14
    17 15
    18 16
    19 17
    20 18
    Max Data

    Excel 2012
    A B C D
    1 Part Serial Number P R
    2
    3 1 D13-00-00-1 0 -0.0031
    4 2 D13-00-00-4 0.0011 -0.0025
    5 3 D13-00-00-5 -0.0005 -0.0033
    6 4 D13-00-00-6 -0.0011 -0.0033
    7 5 D13-00-00-7 0.0009 -0.0028
    8 6 D13-00-00-9 -0.0004 -0.0028
    9 7 D13-00-00-12 -0.0006 -0.0029
    10 8 D13-00-00-13 0.0002 -0.0028
    11 9 D13-00-00-15 -0.0006 -0.0017
    12 10 D13-00-00-16 0.0005 -0.003
    13 11 D13-00-00-17 -0.0008 -0.0028
    14 12 D13-00-00-21 -0.0012 -0.0032
    15 13 D13-00-00-22 0.0001 -0.0025
    16 14
    17 15
    18 16
    19 17
    20 18
    Min Data

    I have a macro that will clear contents on the “Max Data” sheet based on certain criteria. The result will look something like this:

    Excel 2012
    A B C D
    1 Part Serial Number P R
    2
    3 1 D13-00-00-1 0.0168 0.0233
    4 2 D13-00-00-4 0.0256 0.0134
    5 3 D13-00-00-5 0.0296 0.0124
    6 4 D13-00-00-6 0.0281 0.0131
    7 5 D13-00-00-7 0.0225 0.0118
    8 6 D13-00-00-9 0.0294 0.0129
    9 7 D13-00-00-15 0.0288 0.0124
    10 8 D13-00-00-16 0.0291 0.0143
    11 9 D13-00-00-17 0.0199 0.0173
    12 10 D13-00-00-21 0.0268 0.0153
    13 11 D13-00-00-22 0.0189 0.0123
    14 12
    15 13
    16 14
    17 15
    18 16
    19 17
    20 18
    Max Data

    Excel 2012
    A B C D
    1 Part Serial Number P R
    2
    3 1 D13-00-00-1 0 -0.0031
    4 2 D13-00-00-4 0.0011 -0.0025
    5 3 D13-00-00-5 -0.0005 -0.0033
    6 4 D13-00-00-6 -0.0011 -0.0033
    7 5 D13-00-00-7 0.0009 -0.0028
    8 6 D13-00-00-9 -0.0004 -0.0028
    9 7 D13-00-00-12 -0.0006 -0.0029
    10 8 D13-00-00-13 0.0002 -0.0028
    11 9 D13-00-00-15 -0.0006 -0.0017
    12 10 D13-00-00-16 0.0005 -0.003
    13 11 D13-00-00-17 -0.0008 -0.0028
    14 12 D13-00-00-21 -0.0012 -0.0032
    15 13 D13-00-00-22 0.0001 -0.0025
    16 14
    17 15
    18 16
    19 17
    20 18
    Min Data


    What I need is a macro that compares the values in Column B of both sheets and clears contents from the “Min Data” sheet if it’s not found on the “Min Data” sheet. Looking at the images above the value in B7 on the “Min Data” sheet is not found in column B on the “Max Data” sheet. I need to clear the contents from the “Min Data” sheet also. The calculations in the last row of both sheets is used elsewhere hence the need to clear contents rather than delete the entire row.

    Any help would be greatly appreciated!

  7. #7
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,791
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    See if this does what you want on a copy of your workbook. Note the comments on sheet names which I assumed to be "Max Data" and "Min Data".
    Code:
    Sub n3wpv()
    Dim S1 As Worksheet, S2 As Worksheet
    Dim R1 As Range, R2 As Range, c As Range
    Set S1 = Sheets("Max Data") '<--change sheet name to suit
    Set S2 = Sheets("Min Data") '<--change sheet name to suit
    Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
    Set R2 = S2.Range("B3:B" & S2.Cells(Rows.Count, "B").End(xlUp).Row)
    Application.ScreenUpdating = False
    For Each c In R2
       If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 3).ClearContents
    Next c
    Application.ScreenUpdating = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  8. #8
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by JoeMo View Post
    See if this does what you want on a copy of your workbook. Note the comments on sheet names which I assumed to be "Max Data" and "Min Data".
    Code:
    Sub n3wpv()
    Dim S1 As Worksheet, S2 As Worksheet
    Dim R1 As Range, R2 As Range, c As Range
    Set S1 = Sheets("Max Data") '<--change sheet name to suit
    Set S2 = Sheets("Min Data") '<--change sheet name to suit
    Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
    Set R2 = S2.Range("B3:B" & S2.Cells(Rows.Count, "B").End(xlUp).Row)
    Application.ScreenUpdating = False
    For Each c In R2
       If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 3).ClearContents
    Next c
    Application.ScreenUpdating = True
    End Sub
    This is close. I need to only look in B3:B44 for the comparison. It's trying to clear contents below B44 which gives me an error because there are merged cells below. The cells to be cleared would be in B3:AC.

  9. #9
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,791
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by n3wpv View Post
    This is close. I need to only look in B3:B44 for the comparison. It's trying to clear contents below B44 which gives me an error because there are merged cells below. The cells to be cleared would be in B3:AC.
    That's the kind of information you should supply at the outset - saves us both time. Try this:
    Code:
    Sub n3wpv()
    Dim S1 As Worksheet, S2 As Worksheet
    Dim R1 As Range, R2 As Range, c As Range
    Set S1 = Sheets("Max Data") '<--change sheet name to suit
    Set S2 = Sheets("Min Data") '<--change sheet name to suit
    Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
    Set R2 = S2.Range("B3:B44")
    Application.ScreenUpdating = False
    For Each c In R2
       If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 28).ClearContents
    Next c
    Application.ScreenUpdating = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  10. #10
    New Member
    Join Date
    Jun 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear contents based on contents in another sheet

    Quote Originally Posted by JoeMo View Post
    That's the kind of information you should supply at the outset - saves us both time. Try this:
    Code:
    Sub n3wpv()
    Dim S1 As Worksheet, S2 As Worksheet
    Dim R1 As Range, R2 As Range, c As Range
    Set S1 = Sheets("Max Data") '<--change sheet name to suit
    Set S2 = Sheets("Min Data") '<--change sheet name to suit
    Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
    Set R2 = S2.Range("B3:B44")
    Application.ScreenUpdating = False
    For Each c In R2
       If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 28).ClearContents
    Next c
    Application.ScreenUpdating = True
    End Sub
    Works perfectly, thank you!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •