Replacement Macro for one column impacting other columns

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25
Hey all,

I'm working with a large macro, and part of the macro is designed to filter col M for "O" then look into column E and modify the data entered in there. Basically, the data in Col E contains several various numbers, some of which have a hyphen and a subsequent number (101-2, 102-6). I wanted to use the following macro to remove the hyphen and any numbers after the hyphen (102-6 becomes 102). I thought that the following formula would be effective:

'Sort OP in data file to remove -2 from E

Workbooks("260 " & CM).Activate
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13

However, when I use it, it seems to be removing data from other columns, such as H, I, & J. Is there something I am doing wrong that is causing the formula to effect other columns?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
What if you add this line
Rich (BB code):
Workbooks("260 " & CM).Activate
Range("A1").Find ("X/#@")
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13
 
Solution

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25
What if you add this line
Rich (BB code):
Workbooks("260 " & CM).Activate
Range("A1").Find ("X/#@")
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13
I'm not familiar with that function. What does it do?
 

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25

ADVERTISEMENT

Does it work?

Forgive the delay in my reply. I actually didn't get a chance to try this out until the monthly adjustments.

To answer your question, it does work. But I'm not sure what it's doing to make things work. If you don't mind, I'm curious what this function is doing to correct the problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
I suspect that you had done a manual search or replace & changed the "Within" box from Sheet to Workbook & that can cause the problem you were encountering.
Putting
VBA Code:
Range("A1").Find ("X/#@")
at the start of the code just resets the "Within" back to Sheet.
I tend to use something like X/#@ as a value that wont be found to be on the safe side.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,705
Messages
5,707,976
Members
421,539
Latest member
zuniBM

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
Top