what's the best way to copy a column of data with data in every other row?

for example, this range could vary, but it's what i was trying to accomplish,
copy all cells at once

Range("B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25"). copy
and then paste them into a different sheet

now this works, but does one cell at time

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
Range("b" & cell.Row).Copy
End If
Next

and so does this:

For i = 5 To Lastrow Step 2
Sheets("sheet1").Range("b" & i).Copy

so how would you dynamically replace the odd numbers in my first example so all
cells are selected at once? and does it even matter if they're done all at once
or a cell at a time?

--


Gary

After you paste a range with some empty cells in it you can use this code to
remove all empty cells:

Dim xRange As Range
Dim xCell As Range

For Each xCell In Sheets("Worksheet1").Range("A2:A25")
If xCell.Value = 0 Then
If xRange Is Nothing Then
Set xRange = xCell
Else
Set xRange = Union(xRange, xCell)
End If
End If
Next xCell
xRange.Delete Shift:=xlUp


--
Thanks
Shawn


"Gary Keramidas" wrote:

what's the best way to copy a column of data with data in every other row?

for example, this range could vary, but it's what i was trying to accomplish,
copy all cells at once

Range("B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25"). copy
and then paste them into a different sheet

now this works, but does one cell at time

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
Range("b" & cell.Row).Copy
End If
Next

and so does this:

For i = 5 To Lastrow Step 2
Sheets("sheet1").Range("b" & i).Copy

so how would you dynamically replace the odd numbers in my first example so all
cells are selected at once? and does it even matter if they're done all at once
or a cell at a time?

--


Gary

Gary,

It depends on what happens when you paste. Sometimes, it is best to copy
and paste in one step rather than many. To do that, you could use something
like

Dim myCRng As Range
Dim cell As Range

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
If myCRng Is Nothing Then
Set myCRng = cell
Else
Set myCRng = Union(myCRng, cell)
End If
End If
Next

myCRng.Copy

HTH,
Bernie
MS Excel MVP



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
what's the best way to copy a column of data with data in every other row?

for example, this range could vary, but it's what i was trying to
accomplish, copy all cells at once

Range("B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25"). copy
and then paste them into a different sheet

now this works, but does one cell at time

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
Range("b" & cell.Row).Copy
End If
Next

and so does this:

For i = 5 To Lastrow Step 2
Sheets("sheet1").Range("b" & i).Copy

so how would you dynamically replace the odd numbers in my first example
so all cells are selected at once? and does it even matter if they're done
all at once or a cell at a time?

--


Gary

thanks, bernie, it was the union function i was looking for

--


Gary


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Gary,

It depends on what happens when you paste. Sometimes, it is best to copy and
paste in one step rather than many. To do that, you could use something like

Dim myCRng As Range
Dim cell As Range

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
If myCRng Is Nothing Then
Set myCRng = cell
Else
Set myCRng = Union(myCRng, cell)
End If
End If
Next

myCRng.Copy

HTH,
Bernie
MS Excel MVP



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
what's the best way to copy a column of data with data in every other row?

for example, this range could vary, but it's what i was trying to accomplish,
copy all cells at once

Range("B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25"). copy
and then paste them into a different sheet

now this works, but does one cell at time

For Each cell In Range("b5:b25")
If cell.Row Mod 2 = 1 Then
Range("b" & cell.Row).Copy
End If
Next

and so does this:

For i = 5 To Lastrow Step 2
Sheets("sheet1").Range("b" & i).Copy

so how would you dynamically replace the odd numbers in my first example so
all cells are selected at once? and does it even matter if they're done all
at once or a cell at a time?

--


Gary