Shame Question on copying lines after the next line

  • Thread starter Thread starter daniroy
  • Start date Start date
D

daniroy

I did forgot even the vert basics, so please excuse the next question
which is very easy or so I think. I have a sheet. If one criteria is
met, I copy the line, and paste it in another sheet, after the last
line already copied. Up to now my code is.

If Len(Range("b" & i)) = 79 Then
Range("A" & i, "D" & i).Select
Selection.Copy
Sheets("Exports").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveSheet.Paste
Range("A1").Select
Sheets("EasyScreen Datas").Select

A big part of that is useless, I realize, and moreover, I am only
crashing the previous line as I copy the new one on the same place.


After:

If Len(Range("b" & i)) = 79 Then
Range("A" & i, "D" & i).Select
Selection.Copy
Sheets("Exports").Select
Range("A1").Select

What should I do...

I know it is not a sophisticated question ...
 
Maybe...

dim DestCell as range
dim i as long

with worksheets("exports")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

with sheets("easyscreen datas")
for i = 1 to 10 'how are you looping
if len(.range("b"&i).value) = 79 then
.range("A"&i).resize(1,4).copy _
destination:=destcell
set destcell = destcell.offset(1,0)
end if
next i
end with

It uses the first empty cell after the last used cell in column A of the exports
sheet. And .resize(1,4) means to make that single cell (.range("A"&I)) range 1
row by 4 columns (A:D).
 
hi Dave and Thanks
I receive error messages for that line ...
set destcell = .cells(.rows.count,"A").end(xl­up).offset(1,0)
 
There is no dash in xl-up.

It's one word (xlup).

(Google seems to insert dashes where they don't belong.)
 
thanks Dave, it works fine, with a another minor modification on
Set DestCell = Worksheets("exports").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)


full macro now is:

Sub ExtractTrades()

Dim DestCell As Range
Dim i As Long


With Worksheets("exports")
Set DestCell = Worksheets("exports").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
End With


With Sheets("easyscreen datas")
For i = 1 To 500 'how are you looping
If Len(.Range("b" & i).Value) = 79 Then
.Range("A" & i).Resize(1, 4).Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)
End If
Next i
End With



End Sub
 
This portion:

with worksheets("exports")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

uses with/end with. That means that all those range objects (.cells, .rows)
refer back to that sheet in the previous With statement.

I like to qualify the ranges but if you like your change, you could use:

Set DestCell = Worksheets("exports").Cells(Rows.Count,"A") _
.End(xlUp).Offset(1, 0)

There's no need for the With/end with in your code.

And you're looping through rows 1 to 500. If your data ends on row 88, then you
waste a lot of time on those other rows.

If you want to get all the rows that have something in them in column B:

Option Explicit
Sub ExtractTrades()

Dim DestCell As Range
Dim i As Long
dim LastRow as long

With Worksheets("exports")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Sheets("easyscreen datas")
lastrow = .cells(.rows.count,"B").end(xlup).row
For i = 1 To LastRow
If Len(.Range("b" & i).Value) = 79 Then
.Range("A" & i).Resize(1, 4).Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)
End If
Next i
End With

End Sub
 
Back
Top