In Excel How do I prevent an empty cell being created when I am building a list from several sources

In Excel I have written code that works fine as long as the first Administration list, that is copied to build up a combined list (in the sheet Payroll nos), is populated.
If the first list is not populated and the second list is populated my macro 'pastes' the second list one cell down from the first cell (in Payroll nos) instead of in the first cell.
The result is that the final code to remove duplicates and put the results into a master list in column D leaves the master list empty.

As I said the code works perfectly well as long as the first list has some data
Who is Participating?
nutschConnect With a Mentor Commented:
First, get read of all the select statements, e.g.

Sheets("P Administration A").Select

Open in new window


Sheets("P Administration A").Range("B2:B500").Copy

Open in new window

That's not going to fix your macro, but it's going to make your code faster, easier to read and to maintain.

for your nextrow, after each

nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Open in new window


if nextrow=2 and len(cells(1,1))=0 then nextrow=1

Open in new window

DAVID131Author Commented:
Spot on Thomas works a treat
Thanks for rapid response
Glad to help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.