Dynamic rows in selection

[yaroextasy]

Hi,

I am a beginner at VBA however after reading a book and watching many videos I started to work hard to create own macro that would speed-up my work in the office. I have the excel spreadsheet with the data retrieved from another spreadsheet. I have already sorted all values based on vlookup function however I want to cut and paste to different tab only rows having value found in vlookup, all #N/A" should stay in place. My value searching is conducted in column "K" row by row until macro reaches "N/A" in the row meaning no value was found. Just want to point that range is dynamic and number of rows is changing every day. I have written initial VBA code however is not working ... Can you please assist me with this matter?

Sub cutt()
Dim rng As Range
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("A1:K" & lastRow)
rng = Range("A1:K" & lastRow).Select


Range("A2").Select
For i = 1 To rng
If Cells(i, 11).Text <> "#N/A" Then
Cells(i, 11).EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
End If
Next

End Sub

[Tinbendr]

Welcome to the board!

Please use code tags when posting code.

In your studies, look for Guru's who teach using Range instead of Selection. Code execution is faster and you won't have as many suprises. Yes, I know, Excel generates code using Selection, but it has to since everything is created without defined values.

In your code, you're not pasting anything.

Please Login or Register  to view this content.

[yaroextasy]

Hello David,

Thanks a lot for your assistance, it works now! What I want to do next is delete previously copied rows and shift remainings up. It should be pretty the same code however I encountered some problems. Will try to figure out it however if I fail then I would appreciate your help once again,
Thanks mate!

[Tinbendr]

Please Login or Register  to view this content.