Gavin has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Macro for changing names
Hello,
In one column of a table I have Departmental Names.
"Drama" has now become "Theatre and Performance".
So each week I need to:
filter for "Drama"
type in "Theatre and Performance"
Drag this into all filtered cells below.
How would would I automate this as the number of "Drama"s returned when filtered will differ each week? i.e. the table will increase with time.
Thanks for your help,
Gavin
RE: Excel Macro for changing names
Hello Gavin,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding changing names.
I would use the 'Find and Replace' method which will work for the entire sheet. You can create a macro to do this automatically each time it is required.
Of course, if there are other instances of 'Drama' in other columns that shouldn't change, then a little VBA coding will be needed.
Try the 'Find & Replace' method and see if this works for you.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: Excel Macro for changing names
Hello Rodney,
Thanks for this.
Unfortunately, the word "Drama" will crop up in another column for the course titles.
Gavin
RE: Excel Macro for changing names
Hello Gavin,
Yes, the solution I gave you won't work if there are other columns containing the word 'Drama', so what I suggest is that you create a macro that follows the exact steps you outlined, except when you copy the cell which has replacement text down to the filtered cells below, make sure that you don't drag on the bottom right fill-handle... simply double click it which then copies all the way to the bottom cell.
Whenever you run the macro it will automatically include all the items in the filtered list. Your macro should also include clearing the filter after the name change has been done.
Give this a try!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
