VBA macro to copy

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

Nick's picture

not sure whether you're

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"?

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

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

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

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