Empty cell range and error 91
 

My sheet called "Names" have a list of things in two ranges (B8:B37,D8:D37)
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,D8:D37")

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

End Sub

Empty cell range and error 91
 

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,D8:D37")

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

"abdrums" wrote in message
...
My sheet called "Names" have a list of things in two ranges
(B8:B37,D8:D37)
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,D8:D37")

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

End Sub