#### logandiana

##### Board Regular

- Joined
- Feb 21, 2017

- Messages
- 94

For example:

A | B | C | D | E | F | G | H | I | |

1 | Sam | 10 | =if(A1="","",A1) | =if(A1="","",B1) | |||||

2 | Dave | 20 | =if(A2="","",A2) | =if(A2="","",B2) | |||||

3 | Fred | 30 | =if(A3="","",A3) | =if(A3="","",B3) | |||||

4 | Harry | 40 | =if(A4="","",A4) | =if(A4="","",B4) | |||||

5 | =if(A5="","",A5) | =if(A5="","",B5) | |||||||

6 | =if(A6="","",A6) | =if(A6="","",B6) | |||||||

7 | =if(A7="","",A7) | =if(A7="","",B7) | |||||||

8 | =if(A8="","",A8) | =if(A8="","",B8) | |||||||

9 | =if(A9="","",A9) | =if(A9="","",B9) |

<tbody>

</tbody>

I copy columns E:F (or I copy using a named range with a Counta formula to find the last row). When I Paste Special Values in to the new worksheet in A1 I get the following. Everything looks right from here...

A | B | C | D | E | F | G | H | I | |

1 | Sam | 10 | |||||||

2 | Dave | 20 | |||||||

3 | Fred | 30 | |||||||

4 | Harry | 40 | |||||||

5 | |||||||||

6 | |||||||||

7 | |||||||||

8 | |||||||||

9 |

<tbody>

</tbody>

Now using LastRow = Cells(Rows.Count,1).End(xlUp).Row

The LastRow shows to be 9...

But I am scratching my head here.

I thought the way the LastRow worked was to go down to bottom of the worksheet and then back up to the last row where there's something in the cell.

It seems to me that it should have come back up to Row 4 (which is where I want it to be) since there's nothing in cells 5-9.

Can someone explain this and help me figure out how I can get this to work for me?