I have hundreds of ActiveWindow.ScrollColumn lines from my recorded macro, how do I get rid of them easily?

Man_Over_Game 0
Viewed 46
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
Range("BQ1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53

I have that, all over my code.

I thought about using the Find & Replace feature to remove all of the ScrollColumn lines, but I have no idea on how I can make it remove every line without having to specify 60-someodd results.

Is there a way to make it replace every line by accounting for the 1-60 at the end?

replace vba excel
2 Answers
  • Download Notepad++
  • Paste the code in the editor
  • Press Ctrl+H (to find and replace)
  • Check the Search Mode to Regular Expression
  • in search Field enter ActiveWindow.ScrollColumn = [0-9]*
  • If you want to reomove full line, you can enter ActiveWindow.ScrollColumn = [0-9]*$\r\n in the search field
  • and in replace field enter nothing.
  • click replace all
usmanhaq0

I discovered a solution, although @usmanhaq's is better if third-party software can be installed. I'm adding it here for future users who stumble into the same problem I did:

In the Replace feature (CTRL-H), with Pattern Recognition On, have VBA find this line:

  • ActiveWindow.ScrollColumn = [1-9]?

And Replace it with nothing. Then repeat it without the ? at the end.

The first iteration will check anything with two digits (between 10-99), and the second iteration with no wildcard (?) will replace anything with 1 digit (0-9).

Unfortunately, it seems that the Find & Replace feature isn't capable of searching or recognizing line breaks, so this is only able to replace those lines with white space. That does make it easier to remove, but @usmanhaq's solution would be able to remove them entirely.

Man_Over_Game0
Related