Delete selection after pasting into new sheet, paste into first available cell in column

Question

This code that I have only seems to copy, paste, and delete every other row. I'm at a lost any ideas anyone?

Dim c As Long, rG As Range, vR As Variant
c = 1000
Dim wsA As Worksheet, wsC As Worksheet
Set wsA = Sheets("Active")
Set wsC = Sheets("Closed")
wsA.Activate
For Each rG In Intersect(Range("Y:Y"), ActiveSheet.UsedRange)
    vR = rG.Value
    If InStr(vR, "Yes") > 0 Then
        rG.EntireRow.Copy wsC.Cells(c, 1)
        rG.EntireRow.Delete
        c = c + 1
    End If
Next rG

Also the only reason I have C = 1000 is because I don't know how to make it insert into Sheets("Closed") Column A at the end where the first empty cell is. I would rather that because once we have other 1000 cells filled my code with start replacing at cells A1000.

Thanks for any help guys

You need to run the `For` loop **backwards** *(from the bottom up)*, using a numeric index. — Gary's Student, Feb 23 '18 at 22:29
What do you mean I need to run the For loop backwards? I don't know about that. I am not fully advanced with VBA. if I don't have rG.EntireRow.Delete in the code it copies all of the data I need it to do. and pastes it but doesn't delete from active sheet — Joshua Martinez, Feb 23 '18 at 22:35
Your loop approach is good unless you are deleting stuff with the loop. — Gary's Student, Feb 23 '18 at 22:46

cybernetic.nomad · Accepted Answer · 2018-11-08T00:02:42.150

3

Something like:

For i = ActiveSheet.Range("Y" & Rows.Count).End(xlUp).Row to 1 step -1
    set rG = Range("Y" & i)
    vR = rG.Value
    If InStr(vR, "Yes") > 0 Then
        rG.EntireRow.Copy wsC.Cells(c, 1)
        rG.EntireRow.Delete
        c = c + 1
    End If
Next i

You can also fix the value of c with Rows.Count

edited Nov 08 '18 at 00:02

answered Feb 23 '18 at 22:40

cybernetic.nomad

6,100
3
18
31

thanks it works like 99% I still have my c starting at 1000 – Joshua Martinez Feb 23 '18 at 23:01
Dim c As Long, rG As Range, vR As Variant c = 1000 Dim wsA As Worksheet, wsC As Worksheet Set wsA = Sheets("Active") Set wsC = Sheets("Closed") wsA.Activate For i = ActiveSheet.Range("X" & Rows.Count).End(xlUp).Row To 1 Step -1 Set rG = Range("X" & i) vR = rG.Value If InStr(vR, "/") > 0 Then rG.EntireRow.Copy wsC.Cells(c, 1) rG.EntireRow.Delete c = c + 1 End If Next i – Joshua Martinez Feb 23 '18 at 23:01

K.Dᴀᴠɪs · Answer 2 · 2018-02-23T22:54:34.297

You don't necessarily have to step backwards if you add the rows to delete into a special range. Once you finish your loop, just delete the entire special range that we will call delRng.

Dim c As Long, rG As Range, vR As Variant, delRng As Range '<-- New Variable Declaration
c = 1000
Dim wsA As Worksheet, wsC As Worksheet
Set wsA = Sheets("Active")
Set wsC = Sheets("Closed")
wsA.Activate
For Each rG In Intersect(Range("Y:Y"), ActiveSheet.UsedRange)
    vR = rG.Value
    If InStr(vR, "Yes") > 0 Then
        If delRng Is Nothing Then  '<-- Don't use union() if delRng Is Nothing
            Set delRng = rG.EntireRow
        Else
            Set delRng = Union(delRng, rG.EntireRow)
        End If
        rG.EntireRow.Copy wsC.Cells(c, 1)
        c = c + 1
    End If
Next rG

' Delete your delRng - after you finish looping
If Not delRng Is Nothing Then delRng.Delete

You may even notice a performance increase by not deleting these ranges one at a time, but all at once.

For further reading on the usage of the Union() method, see here.

score 1 · Answer 3 · answered Feb 23 '18 at 23:04

1

Dim c As Long, rG As Range, vR As Variant
c = 1000
Dim wsA As Worksheet, wsC As Worksheet
Set wsA = Sheets("Active")
Set wsC = Sheets("Closed")
wsA.Activate
For i = ActiveSheet.Range("X" & Rows.Count).End(xlUp).Row To 1 Step -1
Set rG = Range("X" & i)
vR = rG.Value
If InStr(vR, "/") > 0 Then
    rG.EntireRow.Copy wsC.Cells(c, 1)
    rG.EntireRow.Delete
    c = c + 1
End If
Next I

This is what I have now and it works great, however as you can see I still have c = 1000 every time I try to edit the c I get an error so I don't know how to go about that. Thanks so far for everything you guys rock!

answered Feb 23 '18 at 23:04

Joshua Martinez

35
6

`c = wsC.Range("A" & Rows.Count).End(xlUp).Row + 1` Should give you the first empty row. You can read up more about it [here](https://stackoverflow.com/questions/27065840/meaning-of-cells-rows-count-a-endxlup-row) – cybernetic.nomad Feb 24 '18 at 01:01
Dim c As Long, rG As Range, vR As Variant c = wsC.Range("A" & Rows.Count).End(xlUp).Row + 1 Dim wsA As Worksheet, wsC As Worksheet Set wsA = Sheets("Active") Set wsC = Sheets("Closed") wsA.Activate For i = ActiveSheet.Range("X" & Rows.Count).End(xlUp).Row To 1 Step -1 Set rG = Range("X" & i) vR = rG.Value If InStr(vR, "/") > 0 Then rG.EntireRow.Copy wsC.Cells(c, 1) rG.EntireRow.Delete c = c + 1 End If Next I It says Compile Error: Duplicate declaration in current scope – Joshua Martinez Feb 25 '18 at 03:56
It's hard to tell in your comment (you'd be bettrer off adding new code by editing your question) but that error means you are declaring the same variable twice. Try to declare everything first before doing anything else. – cybernetic.nomad Feb 25 '18 at 05:56

Delete selection after pasting into new sheet, paste into first available cell in column

3 Answers3