Copy from a list with changing row count

Hello,

I hope someone can help me... So, what I'm looking for is a way to copy cells from a list of employees. I have already shortened the list significantly, which is based on the location... from over 700 employees down to a possible 30 rows. Now, I want to be able to copy the exact number of employees to an employees list in my form.

Say the list is from A7 to A36, but one time the location may only have 4 employees and the next time there may be 7 employees or whatever. I tried copying the entire list and pasting the list as values, but it pastes the empty cells as duplicates - conditional formatting is on to show if a name was manually entered twice in the list.

I have a cell showing the number of employees in the location, but I'm not sure of the code to dynamically copy from cell A7:A# based on the employee count. So, I'm always going to start in A7, but the end may change. I thought about using End(xlUp), but it stops at the last formula and I tried searching the internet for the right words, but I have had no luck.

Can anyone show me what to use or lead me in the right direction?

Thanks in advance for any help you can give me...

Jay

Well, I may have found a way around it myself...

I made a dynamic range: =OFFSET(Employees!$AA$7,0,0,Employees!$H$4,1)
Where H4 is the number of employees and then in the macro, I go to it, copy and paste as values. Maybe it's a little crude, but it seems to work.

If there are any other suggestions, I'm open to learning a better way... I'm really a newbie.

Here's the code I used:
Sub Copy_OffsetList()
Application.ScreenUpdating = False
Application.Goto Reference:="OffsetList"
Selection.Copy
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.End(xlDown).Offset(1, 0).Range("A1").Select
Application.ScreenUpdating = True
End Sub

Anyway, thanks... and I am open to any suggestions.
Jay