excel - 查找并删除一行 Excel VBA 代码
问题描述
我有一个 Excel 电子表格,其中包含多个带有多个子过程的模块。一个模块有多个过程,另一个模块调用这些过程中的每一个。问题是 Call Subs 的列表会有所不同。我知道如何添加到下面的例程中,但是根据我在工作表的单元格中输入的内容,我无法弄清楚如何删除一行。该过程是在一个单元格中输入“调用 TMS1707455”,然后运行一个宏,该宏将从下面的子单元中删除该单元格的值。
Sub TMS()
Call TMS1707455
Call TMS1367006
Call TMS4268798
Call TMS1366994
Call TMS39522
Call TMS39523
Call TMS4482313
Call TMS19395
Call TMS39415
Call TMS37118
End Sub
或者,在工作表列中输入此呼叫潜艇列表会更好还是可能。然后,我可以根据需要修改列表,并拥有一个宏,该宏将根据 call subs 列调用所有过程。这样,我就不必有一个宏来添加行和一个宏来删除行。我只是找不到有关如何做任何一种方式的任何信息。
解决方案
这样的事情会对你有很大帮助,我认为这个例子展示了许多编程原则,可以帮助你编写更高效/可管理/可编辑的代码。还要注意评论。
Option Explicit
Sub TMS()
cleanUp "TMS1707455"
cleanUp "TMS1367006"
'... etc
'add / delete comment as needed
End Sub
Sub cleanUp(which As String)
Dim findIt As String, changeTo As String
Select Case which
Case Is = "TMS1707455"
findIt = "1707455(HSPD-12 Sponsor Certification Training)": changeTo = "HSPD-12 Sponsor Certification Training"
Case Is = "TMS1367006"
findIt = "1367006(VA Telework Training Module For Employees)": changeTo = "VA Telework Training Module For Employees"
'Case Is = ' ... next in line '
'delete cases as needed, or comment them out
End Select
Dim ws As Worksheet
Set ws = Worksheets("mySheet") 'always explicitly declare and work directly with objects
ws.Cells.Replace What:=findIt, Replacement:=changeTo
'i would also suggest only working with the cells with actual data you need,
'like the columnset or row set, it will speed up the code, especially using Replace
End Sub
推荐阅读
- swift - 缓存的图像闪烁
- javascript - 用javascript计算时差时面对NaN结果
- hadoop - Hive 截断表需要太多时间
- r - predict.train 与使用配方对象进行预测
- laravel - I have 2 dates in Laravel, how can I run a auto generate post to go through between two dates with time period?
- python-3.x - Contructing new dataframe and keeping old one?
- java - Creating a 2D Array and filling the array with random numbers
- python - What's the best way to modify a variable using a function, but inside of another function?
- ios - ios/index/DataStore 的使用
- laravel - laravel 保持表单数据在按下按钮后不刷新