Loop with VBA and Last Value

[jamesfbeard123]

Hi all,

I have two lists of data.

One list is like this in a column:
1
2
3
4
5
6
7
8
9
10

The second list is like this:

1
2
3

How can I use the loop until function which will find the last value in the first list (10) and continue the list in the second column until the last value found in list one (10).

Help is appreciated.

Thanks,

James

 

[JackDanIce]

Can you give a before and after example? Why not copy list 1 over list 2?

 

[jamesfbeard123]

The before example would be:

1
2
3

and then the loop would see that the last value was 3 and needs to loop down the rows until the last value in the first list (10). reason we cannot copy is there are certian other cells adjacent to the numbers which have data specific to thos enumbers and these are not always sequential (another macro will get rid of certian reuslts we dont need).


So the list could actually start:

100
130
146
147
148
149

But the list may change each run or data may be added in manually in the middle.

 

[JackDanIce]

I'm sorry, it's still not clear what you are attempting to do.

Instead of looping, you can use following to find the last cell in a column of adjacent data:

Range("A1").End(xlDown).Select

This will select the last cell in column A, after row 1 before an empty cell.

E.g. if you had values in A1:A6, it would select A6. If you have values in A1:A8, it would select A8
If you have values in A1:A4 then A5:A15, it would select A4.

If you want to select the very last used cell in a column and you know there are gaps (blank cells) inbetween that and the first row with data, you can use:

Range("A" & Rows.Count).End(xlUp).Select

Aside from this, not sure what else to suggest to resolve your problem.