Vba replace a list of cells with blanks in entire workbook

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
Something like this to get you started

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,263
Office Version
  1. 365
Platform
  1. Windows
.. 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

Active Member
Joined
May 26, 2010
Messages
320
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Give this a try in a copy of your workbook.

Code:
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

Active Member
Joined
May 26, 2010
Messages
320
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Code:
<del>i = 1</del>
i = 5
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,263
Office Version
  1. 365
Platform
  1. Windows
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
Code:
i = Range("startcell").Row
 
Last edited:

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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 ???
 

Watch MrExcel Video

Forum statistics

Threads
1,109,142
Messages
5,527,069
Members
409,742
Latest member
setam

This Week's Hot Topics

Top