在Excel VBA中的某个字符串上方插入空白行 [英] insert a blank row above a certain string in excel vba

问题描述

我有一个包含多张纸的工作簿，每张纸都有不同的产品信息.在每张纸的B列中，最后一行是总计，如果它在B列中找到"TOTALS"，我想插入一个空白行，我想将其插入到TOTALS行的正上方.

I have a workbook containing multiple sheets and each sheet has different products information. In Column B of each sheet, last row is the total I would like to insert a blank row if it found "TOTALS" in column B and I would like to insert it right above TOTALS row.

现在代码可以正常运行了，但是什么也没发生.如果您可以将它们合并为一个过程，则不必调用插入行.

Right now codes run through without any issue but nothing happened neither. I don't necessarily need to call the insert row if you can consolidate them into one procedure.

Sub Main()
  Dim ws As Worksheet

  For Each ws In ActiveWorkbook.Worksheets

  Call insert_row


  Next ws

End Sub


Sub insert_row()
Dim r As Long
  For r = 100 To 1 Step -1
    If Cells(r, 2).Value = "TOTALS" Then Rows(r).Insert
  Next r
End Sub

推荐答案

您需要使用ws作为您的子对象的参数.像这样:

You need to use ws as a parameter to your sub. Something like this:

Sub Main()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        insert_row ws
    Next ws
End Sub

Sub insert_row(ws As Worksheet)
    Dim r As Long
    For r = 100 To 1 Step -1
        If ws.Cells(r, 2).Value = "TOTALS" Then ws.Rows(r).Insert
    Next r
End Sub

这篇关于在Excel VBA中的某个字符串上方插入空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！