XL 2013 find numbers in sheet1 and delete specific columns from row with corresponding number on sheet3

[fmoaveni]

Hi,
First off I would like to say thank you very much for any help that I can get. This is my first time posting to this forum and I hope I can give it enough detail to get some help.

Version: excel 2013

I am having trouble writing a vba code for this and I have looked all over the Internet but cannot seem to find what I need. I have tried modifying different codes for hours as well.
The specific range from is from sheet1 in a workbook (L24 : L60)
I want it to search every row in the sheet called "Eingeben". It is a german workbook but the codes are all in english so far.
From the "Eingeben" sheet (it is technically sheet3 but I have changed the name), if it has a number in column C, I want to automatically delete from that row...columns a,b,e,f,g,h,i.

Example:
Cell L24 on Sheet1 contains 300001
Cell C208 on the Eingeben sheet contains the same 300001 number. It will then delete from the Eingeben sheet A208, B208, E208, F208, G208, H208, and I208. (I want whatever number...in this case 300001 to stay in column C cell 208....This column must always remain constant).

I want it to look at the entire range though from L24:L60 and check the entire Eingeben sheet (column C) against this criteria.

Is that possible?

Thank you again in advance!

 

[fmoaveni]

Anyone have any ideas? I am not sure how else to clarify so if it is confusing maybe someone could ask a question so I can elaborate?

 

[mumps]

Try:

Sub ClearCells()
    Application.ScreenUpdating = False
    Dim Num As Range
    Dim foundNum As Range
    Dim sAddr As String
    For Each Num In Sheets("Sheet1").Range("L24:L60")
        With Sheets("Eingeben").Range("C:C")
            Set foundNum = .Find(Num, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundNum Is Nothing Then
                sAddr = foundNum.Address
                Do
                    Sheets("Eingeben").Range("A" & foundNum.Row).Resize(, 2).ClearContents
                    Sheets("Eingeben").Range("E" & foundNum.Row).Resize(, 5).ClearContents
                    Set foundNum = .FindNext(foundNum)
                Loop While foundNum.Address <> sAddr
                sAddr = ""
            End If
        End With
        Set foundVal = Nothing
    Next Num
    Application.ScreenUpdating = True
End Sub

 

[fmoaveni]

Hi mumps,
Thank you very much for your help. This is going to make a huge difference. The macro works so well but it took about 5 mins to execute. Is that to be expected or can I set a range on it so it only looks at maybe the first 5000 rows?

 

[mumps]

Using your example of searching for the number 300001 in column C, can there be more than one occurrence of the number in column C or is it a unique value in column C?

 

[fmoaveni]

Hi mumps,
The 300001 should be a constant. That number is an example for a space in a warehouse and should never change. That is why I never want it deleted. It should just be unique, however, it may end up having 300001a and 300001b in the future. Will that be a problem?

 

[mumps]

Since the number is unique with the possibility of having an "a" or "b" or any other letters attached, this macro should be faster and also take into account the addition of letters to the number.

Sub ClearCells()
    Application.ScreenUpdating = False
    Dim Num As Range
    Dim foundNum As Range
    For Each Num In Sheets("Sheet1").Range("L24:L60")
        Set foundNum = Sheets("Eingeben").Range("C:C").Find(Num, LookIn:=xlValues, lookat:=xlPart)
        If Not foundNum Is Nothing Then
            Sheets("Eingeben").Range("A" & foundNum.Row).Resize(, 2).ClearContents
            Sheets("Eingeben").Range("E" & foundNum.Row).Resize(, 5).ClearContents
        End If
    Next Num
    Application.ScreenUpdating = True
End Sub

 

[fmoaveni]

mumps,
You are a hero! That works so well! Thank you very much. I am posting another question in a new thread if you have some time. It may be a little more complicated if you think you are up for the challenge
It has to do with a pop up box I have created to input information on the "Eingeben" tab. Thank you!

 

[mumps]

You are very welcome. Send me a private message with the link to your new thread and I'll have a look.