excel 在“T”列中的“True”上方插入一个空行

ryoqjall 于 2023-06-07 发布在其他

关注(0)|答案(1)|浏览(109)

我有一个工作表，它是从另一个工作表填充的，它可以有可变数量的行。在这个工作表中，我试图在上面插入一个空行，“True”位于列“T”中。在D列的空白行中，我想添加“>JOBSTART”
然后我想添加另一个空白行，上面都是“>JOBSTART”，除了WHEN IT IS IN“D2”。在D列的空白行中，我想添加“>JOBEND”。最后一行也会有“>JOBEND”。我已经尝试了多个VBA的，但没有给我的结果，我正在寻找。
我尝试了这个方法，但它只在列表的底部添加了一个空行。

Sub InsertOneRowAboveBlankCells()

Dim sColBlnk As Range, ar As Range
Set sColBlnk = Range("s:s").SpecialCells(xlCellTypeBlanks)
For Each ar In sColBlnk.Areas
ar.Cells(1, 1).EntireRow.Insert
Next
End Sub

excel

来源：https://stackoverflow.com/questions/76408904/insert-a-blank-row-above-true-which-is-in-column-t

1条答案

按热度按时间

bbuxkriu1#

尝试

Sub InsertRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Application.ScreenUpdating = False
    ' Set the worksheet object
    Set ws = ThisWorkbook.Worksheets("Sheet1")   ' modify accrodingly

    ' insert row if value "True"
    For i = ws.Cells(ws.Rows.Count, "T").End(xlUp).Row To 2 Step -1
        If ws.Cells(i, "T").Value = "True" Then
            ws.Rows(i).Insert Shift:=xlDown
        End If
    Next i
    
    'enter jobstart for each blank rows
    For i = 2 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        If ws.Cells(i, "D").Value = "" Then
            ws.Cells(i, "D").Value = ">JOBSTART"
        End If
    Next i

    ' insert row if value jobstart
    For i = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row To 3 Step -1
        If ws.Cells(i, "D").Value = ">JOBSTART" Then
            ws.Rows(i).Insert Shift:=xlDown
        End If
    Next i

    'enter jobend for each blank rows
    For i = 3 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        If ws.Cells(i, "D").Value = "" Then
            ws.Cells(i, "D").Value = ">JOBEND"
        End If
    Next i

    ' Add ">JOBEND" to the last row in column D
    ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, "D").Value = ">JOBEND"
    Application.ScreenUpdating = True
End Sub

赞(0）回复(0）举报 2023-06-07

我来回答

excel 在“T”列中的“True”上方插入一个空行

1条答案

相关问题

热门标签

最新问答