VB Code To Copy Range Of Cells

I am looking for a VB code to copy data from columns A and B to Cells C and D. I need the Data to fill top to bottom in C and D with no skipped rows or filling in extra data.

There may be skipped rows in A an B with no data and A and B are filled with a formula. Can this be done automatically ? Thanks

Hi rockyw,

I don't see a problem but would like to write the correct VBA code for your problem. Can you supply a short sample file. You can attach it by pressing the "Go Advanced" button below the message area and then the PaperClip that apperas above the new advanced message area. This will open a window for you to attach a sample file.

Also, why doesn't sorting and copy values only work?

OK Rockyw,
Find the attached with the VBA code to do what you want that looks like this:

Option Explicit
Sub PackToCDColums()

Dim RowCtrA As Double
Dim LastRowA As Double
Dim LastRowC As Double

'Clear Contents in C and D Columns
LastRowC = Cells(Rows.Count, "C").End(xlUp).Row
Range(Cells(3, "C"), Cells(LastRowC, "D")).ClearContents

'Loop down and copy non blanks from A to C and B to D
LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
For RowCtrA = 3 To LastRowA
    If Cells(RowCtrA, "A") <> "" Then
        LastRowC = Cells(Rows.Count, "C").End(xlUp).Row + 1
        Cells(LastRowC, "C") = Cells(RowCtrA, "A").Value
        Cells(LastRowC, "D") = Cells(RowCtrA, "B").Value
    End If
Next RowCtrA

End Sub

Hi,

YES you can certainly run it manually. If this problem is solved, edit the original post and make the prefix as solved.

You didn't give me the chart so I didn't know to code around any locked chart. Run it manually or upload a sample with the problem if you want me to look at it.

OK RockyW,

You can't have merged cells in C2 and D2. Let them be two separate words. See attached. This may have been part of the problem.

I moved the code to behind the worksheet. Now when anything changes on the second sheet in columns A or B it will fire the macro to rebuild columns C and D. If that doesn't work then double click the mouse in either column A or B and it will rebild C and D.

Let me know. The question with the Worksheet_Change event is I don't know if you physically change anything on this sheet or not. If you never need to double click to fire the macro that would be great. If you need to double click then I need to see more of the spreadsheet and chart etc.

Hi rockyw,

teylyn taught me this one...

Clikc on your chart area and then click on the graph. Right click and find "Select Data..." Click on it and get to the "Select Data Source" dialog box. On the bottom left of this window is a button called "Hidden and Empty Cells". Click this and find the dialog I think you are asking about.
See http://office.microsoft.com/en-us/ex...010048555.aspx

hope that helps.