copy range of cells with blanks then paste without blanks
 

I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob

copy range of cells with blanks then paste without blanks
 

Hi justaguyfromky

With the formulas on A1:A1000 this example copy to C1

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value < "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If

Next
End With

If Not rng Is Nothing Then rng.Copy Range("C1")

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Another option is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob

copy range of cells with blanks then paste without blanks
 

Thanks Ron.

This is exactly what I needed. I appreciate your help.

Rob


"Ron de Bruin" wrote:

Hi justaguyfromky

With the formulas on A1:A1000 this example copy to C1

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value < "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If

Next
End With

If Not rng Is Nothing Then rng.Copy Range("C1")

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Another option is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob

copy range of cells with blanks then paste without blanks
 

Ron,

I came up with a problem when I tried to change the macro to match my data.
The range of cells that I am using is named. The named range may grow
because I have another macro that will insert a new row to add new data. So
in theory, I changed the "A's" in the formula to "Waste" (the name of the
range) and the "C1" to "WasteD" (the destination of the copied cells).
"WasteD" is a single cell. Is this still possible?

Thanks,
Rob

"Ron de Bruin" wrote:

Hi justaguyfromky

With the formulas on A1:A1000 this example copy to C1

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value < "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If

Next
End With

If Not rng Is Nothing Then rng.Copy Range("C1")

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Another option is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob

copy range of cells with blanks then paste without blanks
 

The example loop through row 1 to 1000

StartRow = 1
EndRow = 1000

You can use this for example to loop through all rows in column A with data

StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row

It use the A column now, so change the A to your column


and the "C1" to "WasteD"
That is OK


For more info see
http://www.rondebruin.nl/delete.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
Ron,

I came up with a problem when I tried to change the macro to match my data.
The range of cells that I am using is named. The named range may grow
because I have another macro that will insert a new row to add new data. So
in theory, I changed the "A's" in the formula to "Waste" (the name of the
range) and the "C1" to "WasteD" (the destination of the copied cells).
"WasteD" is a single cell. Is this still possible?

Thanks,
Rob

"Ron de Bruin" wrote:

Hi justaguyfromky

With the formulas on A1:A1000 this example copy to C1

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value < "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If

Next
End With

If Not rng Is Nothing Then rng.Copy Range("C1")

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Another option is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob

copy range of cells with blanks then paste without blanks
 

That was it. I have tested it and it works. Thanks again for your help.

Rob


"Ron de Bruin" wrote:

The example loop through row 1 to 1000

StartRow = 1
EndRow = 1000

You can use this for example to loop through all rows in column A with data

StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row

It use the A column now, so change the A to your column


and the "C1" to "WasteD"
That is OK


For more info see
http://www.rondebruin.nl/delete.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
Ron,

I came up with a problem when I tried to change the macro to match my data.
The range of cells that I am using is named. The named range may grow
because I have another macro that will insert a new row to add new data. So
in theory, I changed the "A's" in the formula to "Waste" (the name of the
range) and the "C1" to "WasteD" (the destination of the copied cells).
"WasteD" is a single cell. Is this still possible?

Thanks,
Rob

"Ron de Bruin" wrote:

Hi justaguyfromky

With the formulas on A1:A1000 this example copy to C1

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value < "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If

Next
End With

If Not rng Is Nothing Then rng.Copy Range("C1")

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Another option is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"justaguyfromky" wrote in message
...
I have a range of cells that have values in some cells and blank in others
(actually there is a formula in all cells, but if there is no value, the
formula returns nothing but a blank cell). I want to copy that range of
cells and paste it in a column without pasting the blank cells. So the copy
range may be 40 cells, but the paste range may only be 15 or 20. How can I
paste this into the destination cells leaving no blanks? Any help would be
greatly appreciated. Thanks, Rob