Copy and paste by condition. Without selecting the particular cell

[ZOL]

I want to the cut and paste to be done by not selecting the cells or activating it. Eg, .select or .activate. It is possible to be done? My code below shows me error on object does not support property or method. Why is this so?

Sub test()
With Sheets("Sheet1")

A = 5
B = 4
        NextRow = Application.WorksheetFunction.CountA(.Range("A:A")) + 1
    
        Set r = .Range(.Cells(2, 1), .Cells(NextRow, 1))
    
        For n = 2 To r.Rows.Count
        If A >= B Then
            If .Cells(n, 2).Value = "" Then
                
                    
                    .Range("A" & "2" & ":K" & "n-1").Cut
                End If
            End If
        Next n
        
        NextRow = Application.WorksheetFunction.CountA(.Range("A:A")) + 1
    
        Set r = .Range(.Cells(2, 1), .Cells(NextRow, 1))
         For n = 2 To r.Rows.Count
         If A >= B Then
            If .Cells(n, 13).Value = "" Then
                
                    RowCount = .Cells(n, 2).Row
                    .Range("M" & RowCount).Paste
                End If
            End If
        Next n

End With

End Sub

 

[jbeaucaire]

This is the only blatant error I found:

                .Range("A2:K" & n - 1).Cut

But if you describe verbally what you're doing, I'm pretty sure it can be done without the looping, too.

 

[ZOL]

It will have to check for the empty cell then minus one row up.
They didnt direct me into the .Range("A2:K" & n - 1).Cut when the cell is empty. what did i does wrongly?

 

[jbeaucaire]

Can you describe verbally your goal without referring to macros? Tell me what you're trying to do.

 

[ZOL]

My goal is to check for the last row... eg.. last row is 4. then it will cut frm the range A2 to the K4

 

[ZOL]

When i change the code to the below one, it worked for me. why is it so?

Set r = .Range(.Cells([COLOR="Red"]1[/COLOR], 1), .Cells(NextRow, 1))

 

[jbeaucaire]

I dont' know, I still don't know enough about what you're doing. Are you saying basically... "I want to copy find the last row of data, for example row 4, then cut from A1:K4 and paste it ....where?"

If all you want to do is find the last row with data, that's easy:

Dim LR

LR = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:K" & LR).Cut

It looks like you're trying to paste in column M. If you want to find the next empty cell in column M as the starting point, it's the same approach:

Dim LR as Long, NR as Long

LR = Range("A" & Rows.Count).End(xlUp).Row
NR = Range("M" & Rows.Count).End(xlUp).Row + 1 

Range("A1:K" & LR).Cut Range("M" & NR)

 

[ZOL]

Yup, that's what i want to do.. Do you know why when I tried to change the code above to 1 and it works?

 

[jbeaucaire]

Cells(1,1) is equivalent to "A1".