Hi!
I'm working on a code to cut rows which have in column 4 the name "BS", to the end of my data (3 rows below). I wrote this code, but it has an error on the highlighted line "Apllication defined or object defined error".
Can you please help me on this one?
Thank you
Ines
Sub Retirar_BS()
'Define lastrow
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Select
LastRow = ActiveCell.Row
'Define variables
reference = "BS"
i = 4
'Cut and paste to the end of the table
While i <= Cells(Rows.Count, 4).End(xlUp).Row
If Cells(i, 4) = reference Then
Cells(i, 4).EntireRow.Cut Destination:=Cells(A & LastRow)
LastRow = LastRow + 1
Else
i = i + 1
End If
Wend
End Sub
Last edited by nespena; 09-10-2010 at 10:10 AM.
It would be like this:
Cells(i, 4).EntireRow.Cut Destination:=Cells(lngLastRow, "A")
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Still doesn't work... the same error on the same line
Could it be because when I cut the line the addresses referenced to in the code move? Should I just copy them to the end, and then delete the original ones?
Maybe:
Sub Retirar_BS() Dim lngLastRow As Long, lngWriteRow As Long Dim lngLoopRow As Long 'Define variables lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row lngWriteRow = lngLastRow + 3 'Cut and paste to the end of the table For lngLoopRow = 4 To lngLastRow If Cells(lngLoopRow, 4) = "BS" Then Rows(lngLoopRow).Cut Destination:=Cells(lngWriteRow, "A") lngWriteRow = lngWriteRow + 1 End If Next lngLoopRow End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Thank you so much, it worked!
Now I'll just a piece of code that will delete all the blank rows.
Sub Retirar_BS() Dim lngLastRow As Long, lngWriteRow As Long Dim lngLoopRow As Long 'Define variables lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row lngWriteRow = lngLastRow + 3 'Cut and paste to the end of the table For lngLoopRow = 4 To lngLastRow If Cells(lngLoopRow, 4) = "BS" Then Rows(lngLoopRow).Cut Destination:=Cells(lngWriteRow, "A") lngWriteRow = lngWriteRow + 1 End If Next lngLoopRow For lngLoopRow = lngLastRow To 4 Step -1 If Cells(lngLoopRow, 4) = "" Then Rows(lngLoopRow).Delete End If Next lngLoopRow End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks