Home » Forums » Open Source Learning Center » Question and Answer

VBA macro to copy

Submitted by Thomas Collins on 30 September, 2012 - 17:00

I want to copy a range of data/blanks in one column of one worksheet to a second worksheet, eliminating any blank rows. Can this be done?
Tom Collins

‹ COMPLETE EXCEL STEPS TO CONNECT YOUR DATA? Need help with FLOOR function ›
»
Nick's picture

not sure whether you're

Submitted by Nick on 30 September, 2012 - 18:48.

not sure whether you're talking about VBa or Excel functions..
to copy with VBA:
http://excelexperts.com/copy-values-vba

to remove blank rows, just sort the data, blank rows fall to the bottom.

»

Can I make the following "cell" version to apply to a "range"?

Submitted by Thomas Collins on 30 September, 2012 - 19:03.
This routine works for say one cell: 
Sub CopyRange()
With Worksheets("Results")
ActiveCell.Copy Destinations: _
.Range("A" & .Rows.Count).End(x1Up).Offset(1,0)
End With
End Sub
Can it be modified to allow a range of cells, I guess is my question. TPC
»
Nick's picture

well.. a variant of it will

Submitted by Nick on 30 September, 2012 - 19:42.
well.. a variant of it will work: 
Sub CopyRange()
With Worksheets("Results")
Selection.Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
End With
End Sub
change "Selection" to whatever you want
»

Sorry this is new to me. I

Submitted by Thomas Collins on 30 September, 2012 - 21:06.

Sorry this is new to me. I have to keep the order of the results, so the first recommendation I can't use. The CopyRange above does copy to the destination worksheet but does not remove the blank rows in that range...

I'm trying to select a range on the first worksheet and copy it to the second sheet without blank rows.
Should I go back to CopyCell() idea, and have it repeat down the column?
tpc

»
Nick's picture

ok, so once copied over... if

Submitted by Nick on 30 September, 2012 - 21:55.
ok, so once copied over... if you sort the range to remove the blanks, does that work ? If so, can use something like this: 
' sub to run everything
Sub RunIt()
    Call RemoveBlanks
    Call CopyRange
End Sub
 
' Sort the selection, and remove blanks
Sub RemoveBlanks()
    Selection.Sort Key1:=Selection.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
End Sub
 
' Copy over and put on results sheet
Sub CopyRange()
    With Worksheets("Results")
    Selection.Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
    End With
End Sub
»