Vba replace a list of cells with blanks in entire workbook

[rhombus4]

I have a list of data in column x in sheet1 data starts in X1 and goes down to x20

what I need is 1 by 1 search the values in X1 to x20 and try to find that value in the 1st column of every sheet and delete that value. If it can't find it don't do anything and if a cell in X1 to x20 is blank then exit the sub

thanks

 

[Johnny Thunder]

Something like this to get you started

Sub forEachWs()
    Dim ws 		As Worksheet
	Dim r		as range
    
	
	For Each ws In ActiveWorkbook.Worksheets
	
	For each r in ws.Range("X1:X20")
		If r = 'First Column Value
			'Delete Value
		Else
	'Do nothing
		next r
	
	Next
	
End Sub

 

[Peter_SSs]

.. try to find that value in the 1st column of every sheet ..

Some clarification is required.
If one of the values to look for is "cat"

Do we delete if a cell is found with "The cat sat on the mat"? If so, are we just deleting cat leaving "The sat on the mat" or delete the whole cell?

Do we delete if a cell is found with "Tom was scathing"? If so, are we just deleting cat leaving "Tom was shing" or delete the whole cell?

Your thread title mentions replacing but your description is about deleting. Can you clarify that issue too please?

 

[rhombus4]

Apologies for not being clearer.

Will actually need to search for only cells that match and will also be a number i.e. 12345678 or 12357681 etc. Once found those figures can be deleted/cleared or replaced with "" from whichever sheet they are on including duplicates

 

[Peter_SSs]

Give this a try in a copy of your workbook.

Sub Delete_Values()
  Dim ws As Worksheet
  Dim i As Long
  Dim vVal As Variant
  
  i = 1
  Do While Not IsEmpty(Sheets("Sheet1").Range("X" & i).Value)
    vVal = Sheets("Sheet1").Range("X" & i).Value
    For Each ws In Worksheets
      If ws.Name <> "Sheet1" Then ws.Columns("A").Replace What:=vVal, Replacement:="", LookAt:=xlWhole
    Next ws
    i = i + 1
  Loop
End Sub

 

[rhombus4]

Thanks peter. Works perfectly. One quick thing if the data started in X5.
I tried range("x5" & I).value but it didn't work.

 

[Peter_SSs]

Thanks peter. Works perfectly. One quick thing if the data started in X5.
I tried range("x5" & I).value but it didn't work.

Change

<del>i = 1</del>
i = 5

 

[rhombus4]

Thanks again but the problem I have is sometimes people insert rows so the position moves. And code doesn't update to reflect those changes

Can it be changed so the 1st cell is a named cell. I.e. call X5 startcell

 

[Peter_SSs]

Thanks again but the problem I have is sometimes people insert rows so the position moves. And code doesn't update to reflect those changes

Can it be changed so the 1st cell is a named cell. I.e. call X5 startcell

Yes, if we are only worried about rows being inserted or deleted then change that same line to

i = Range("startcell").Row

 

[rhombus4]

Thanks peter. Just tried the same code on a different workbook which has some hidden sheets and something weird happened. The values were removed in the summary sheet and didn't remove them from the other sheets ???