copy range of cells with blanks then paste without blanks

G

Guest

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
 
R

Ron de Bruin

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
 
G

Guest

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
 
R

Ron de Bruin

The example loop through row 1 to 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top