edited-- if statement vba -- how to skip a cell if it has a value

Question

I've edit my original post and given you more details. Basically I have this code:

For x = 1 To 1000
If track.Cells(x, 1) = Date Then
    If track.Cells(x, 2) = "EU" Then
        If track.Cells(x, 3) = "ABCDE" Then
           ST.Range("ABCDE").Copy
           EU.Cells(41, 2).PasteSpecial
           Call ActivateSheet
           track.Range(Cells(x, 4), Cells(x, 9)).Copy
               **For i = 42 To 1000
                  If EU.Cells(42 + i, 2) Then
                     EU.Cells(42 + 1, 2).PasteSpecial
                  End If
               Next i**
        End If
    End If    
End If Next x

And I'm having problems with this specific part of the code:

For i = 42 To 1000
                  If EU.Cells(42 + i, 2) Then
                     EU.Cells(42 + 1, 2).PasteSpecial
                  End If
               Next i

What this basically does is copy a range of cells from another worksheet on the same workbook and it should place it on another worksheet. Now, what happens with the code i'm having problems is, I want it to paste to the row below AFTER it pastes the first one. Are there any other ways I can achieve this? I'm only starting to self teach myself in VBA coding. Any advice would be greatly appreciated.

Answer 1

You don't need to loop the values to find the bottom row, you can simply come from the bottom up in one go.

replace this:

**For i = 42 To 1000
   If EU.Cells(42 + i, 2) Then
      EU.Cells(42 + 1, 2).PasteSpecial
   End If
Next i**

With this:

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial

How it works:

Cells(rows.count,2) is the very last row in the sheet and column 2 (B1048576 nowadays or B65536 in the old days)

.End(XLup) tells it to go up until data, exactly like CTRL-UP in Excel

This tells us the last row that contains data, we don't want to paste here because you will overwrite your last row so:

.offset(1,0) tells it to go ONE row and zero columns away from where it is.

This has worked out the address of where you need to paste, then we pastespecial.

Hope that helps

To actually see how this works go to the immediate window in the VB editor using CTRL-G and paste the following code:

?Cells(Rows.Count, 2).address
?Cells(Rows.Count, 2).End(xlUp).address
?Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).address

Hit enter after each line and you will see how each added portion of code to the line alters the results