Forum Discussion

Jo McCarthy's avatar
Jo McCarthy
Copper Contributor
Jul 05, 2018

Macros Help Please! I need to delete one word in a cell

Hello from a Macro newbie! 

I have 3 columns: 

A - Description 

B - Number of items (e.g.: 6,000 items) 

C - Modified date 

 

I would like to use a macro (rather than Find + Replace) to delete the word "items" from column B. I have over 25 spreadsheets hence the need for a macro. 

 

My macro name is 'RemoveWordItem'. 

 

I am sure it's simple, yet I can't seem to figure it out...

 

Any ideas?! 

 

Many thanks, Jo 

 

 

 

 

 

Rather than using Find + Replace, I would like to use a macro to delete the word

excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jul 05, 2018

    This macro does the trick (assuming the files are in a single folder and the items to replace are in a single sheet in each workbook):

    Sub RemoveWordItem()
    Dim lCount As Long
    Dim vFileName As Variant
    Dim sPath As String
    Dim lFilecount As Long
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFileName = Application.GetOpenFilename("Microsoft Excel files (*.xls*),*.xls*", , "Please select the file(s) to open", , True)
    If TypeName(vFileName) = "Boolean" Then Exit Sub
    For lCount = LBound(vFileName) To UBound(vFileName)
        ProcessFile CStr(vFileName(lCount))
    Next
End Sub


Sub ProcessFile(sFileName As String)
    Dim oSh As Worksheet
    Workbooks.Open sFileName
    With ActiveWorkbook.Worksheets("YourSHeetNameGoesHere")
        .Range("B:B").Replace What:="items", Replacement:="", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False
    End With
    ActiveWorkbook.Close True
End Sub

    Make sure you enter the right sheet name instead of "YourSHeetNameGoesHere"

    • Jo McCarthy's avatar
      Jo McCarthy
      Copper Contributor
      Jul 09, 2018

      Hi Jan

       

      I am grateful for your suggestion but it didn't work :-(

       

      I have been trying to use a 'find and replace' type solution within the macro.

      Sub itemReplace()
      '
      ' RemoveWordItem Macro
      ' Remove the words 'item' and 'items' from column B so that only the number remains.
      'Variable created which is good practice but not necessary the key here is the wild card *.


      Dim itemReplace As String


      itemReplace = "item*"


      ' You can choose however many columns you want from a to zz if you so desire.


          Columns("B:B").Select


          'itemReplace in the following line is a variable create from the text above.


          Selection.Replace What:=itemReplace, Replacement:=" ", LookAt:=xlPart, _


              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _


              ReplaceFormat:=False

      '
      End Sub

       

      this isn't working either!

       

      Any ideas?

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jul 09, 2018
        What do you mean by "it isn't working" exactly?

Resources