Empty cell range and error 91

[Guest]

My sheet called "Names" have a list of things in two ranges (B8:B37,D837)
and some of the cells are empty. I need to copy the list from the two ranges
to, for
example, in B12:B41 on sheet "Attendance" without the empty cells. I know
that the range on sheet 2 is smaller than the range on sheet1. This is
because I have two list of name depend of the employee contract type but
never the total of the sum of both will be more than 30.
-----------------------------------------------------------------------------------------------
Sub COPY()

Dim rngN1 as Range

Set rngN1 = Sheets("Names").Range("B8:B37,D837")

If Not rngN1 Is Nothing Then
TempN = rngN1
With Temp
.Copy Destination:=Sheets("Attendance").Range("B12:B41")
End With
End If

End Sub

 

[Bernie Deitrick]

abdrums,

It is a bad idea to name a sub with a key word like "Copy".... Anyway,
simply step through the filled cells like so:

Sub CopySub()
Dim rngN1 As Range
Dim myCell As Range
Dim i As Integer

Set rngN1 = Sheets("Names").Range("B8:B37,D837")

i = 1

For Each myCell In rngN1.SpecialCells(xlCellTypeConstants)
myCell.Copy Destination:=Sheets("Attendance").Range("B12:B41").Cells(i)
i = i + 1
Next myCell

End Sub


HTH,
Bernie
MS Excel MVP