Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
860 views
in Technique[技术] by (71.8m points)

excel - Cutting Row with Data and moving to different sheet VBA

I'm trying to cut a row that has the specified cell blank and then paste it into another sheet in the same workbook. My coding works fine to delete the row but everything I've tried to cut and paste keeps giving me errors. Here's the working code that deletes the rows:

Sub Remove()

    'Remove No Denovo &/or No Peak Seq
    Dim n As Long
    Dim nLastRow As Long
    Dim nFirstRow As Long
    Dim lastRow As Integer

    ActiveSheet.UsedRange

    Set r = ActiveSheet.UsedRange
    nLastRow = r.rows.Count + r.Row - 1
    nFirstRow = r.Row

    For n = nLastRow To nFirstRow Step -1
        If Cells(n, "G") = "" Then Cells(n, "G").EntireRow.Delete
    Next n

End Sub

Thanks for any help!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If you want to cut and paste, see below:

With Sheet1
    .Range("A1").Cut Sheeet2.Range("A1")
End With

Above cut's value in Cell A1 in Sheet1 and paste it on Cell A1 of Sheet2.
Now if you want to incorporate it in your code, see below.

Dim i As Long: i = 1
With Activesheet
    For n = nLastRow To nFirstRow Step -1
        If .Cells(n, "G") = "" Then
            .Cells(n, "G").EntireRow.Cut Sheet2.Cells(i, "A")
            .Cells(n, "G").EntireRow.Delete '~~> if you want to delete
            i = i + 1
        End If
    Next
End With

Above code cuts all rows with Cell in G blank and paste it in Sheet2 starting A1.
Note: Sheet2 and Sheet1 that I used above are sheet codenames.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...