Add Text to String in a Filtered List

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
608
Office Version
  1. 365
Platform
  1. Windows
Hello,
I’ve been using the code below to add text to the beginning of a string in Column C. How do I run it after I filter out anything I don’t want?

I tried replacing;
Code:
Range(addr) = Evaluate("IF(" & addr & "="""","""",""" & "Inventory" & """&" & addr & ")")

With

Code:
Range(addr).SpecialCells(xlCellTypeVisible) = Evaluate("IF(" & addr & "="""",""""," & TextToPrefix & """&" & addr & ")")

But it gave me strange results.

Any help is appreciated.


Code:
Sub AddToBeginningOfText()
  Dim lastRow As Long, addr As String
  
  Const TextToPrefix As String = "Inventory - "
  Const ColumnToProcess As String = "C"
  Const Startrow As Long = 2
  
   'Filter out unwated text
  'Range("A2").AutoFilter Field:=3, Criteria1:="=*(Int)*", Operator:=xlAnd
   
  lastRow = Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
  addr = Cells(Startrow, ColumnToProcess).Resize(lastRow - Startrow + 1).Address
    
  'adds to begining of string
  Range(addr) = Evaluate("IF(" & addr & "="""","""",""" & "Inventory" & """&" & addr & ")")
    
       
End Sub

Here's an example of the spreadsheet (Results are in Column D)
Excel Workbook
ABCD
1DateReasonResults should be:
21/4/2017206 (Int)Inventory - 206 (Int)
31/5/2017206 (Int)Inventory - 206 (Int)
41/4/2017206 (Int)Inventory - 206 (Int)
51/5/2017206 (Int)Inventory - 206 (Int)
61/5/2017VacationVacation
71/6/2017VacationVacation
81/8/2017VacationVacation
91/4/2017208 (Int)Inventory - 208 (Int)
101/5/2017208 (Int)Inventory - 208 (Int)
111/4/2017208 (Int)Inventory - 208 (Int)
121/5/2017208 (Int)Inventory - 208 (Int)
131/4/2017225 (Int)Inventory - 225 (Int)
141/5/2017VacationVacation
151/6/2017VacationVacation
161/8/2017VacationVacation
171/5/2017225 (Int)Inventory - 225 (Int)
181/4/2017225 (Int)Inventory - 225 (Int)
191/5/2017225 (Int)Inventory - 225 (Int)
201/4/2017198 (Int)Inventory - 198 (Int)
211/5/2017198 (Int)Inventory - 198 (Int)
221/4/2017198 (Int)Inventory - 198 (Int)
231/5/2017198 (Int)Inventory - 198 (Int)
Sheet2
Excel 2010
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
your addr variable conflicts with the SpecialCells range in that addr is for the entire range including the hidden cells. I am surprised you got any results other than an error message. SpecialCells has limited properties and methods since in many cases the ranges will be non-contiguous.
 
Upvote 0
Can you run it before you filter?

If not, you might want to copy the post-filtered cells to a new sheet where it will be a contiguous block. Thus avoiding the problem JLGWhiz is talking about.
 
Upvote 0
This will add the text in the cell but I want it to include what’s already in the cell.

Code:
With ActiveSheet
    .Range("A1").AutoFilter Field:=3, Criteria1:="=*(Int)*"
    lastRow = .Range("C" & Rows.Count).End(xlUp).Row
    .Range(.Range("C2"), .Range("C2:C" & lastRow)).SpecialCells(xlCellTypeVisible).Value = "Inventory - "
     
End With
 
Upvote 0
Normally to add the previous contents you make it first equal to itself and then concatenate with &

Like this:
Code:
.Range(.Range("C2"), .Range("C2:C" & lastRow)).SpecialCells(xlCellTypeVisible).Value = "Inventory - " & .Range(.Range("C2"), .Range("C2:C" & lastRow)).SpecialCells(xlCellTypeVisible).Value

but that is hideous and I can't guarantee it will work for you.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top