Results 1 to 3 of 3

Copy and Paste and Delete Columns Based on Criteria

Threaded View

datalogger Copy and Paste and Delete... 12-20-2010, 06:23 PM
antoka05 Re: Copy and Paste and Delete... 12-21-2010, 09:01 AM
datalogger Re: Copy and Paste and Delete... 12-21-2010, 01:07 PM
  1. #1
    Registered User
    Join Date
    12-20-2010
    Location
    SLC, UT
    MS-Off Ver
    Excel 2007
    Posts
    2

    Copy and Paste and Delete Columns Based on Criteria

    I need to reformat my data. Right now it look slike this (only 3 rows, but thousands of columns):

    A B C D E F
    1 mat 1 mat 2 mat 3 mat 4
    2 equip 1 equip 2 equip 3 equip 4 equip 5 equip 6
    3 equip Info equip Info equip Info equip Info equip Info equip Info

    Where row 1 is blank, this data in the column is associated with the preceding "mat#". I need to cut and paste the extra equip # and equip info under the corresponding mat# column and omit the blank columns. The reformatted spreadsheet should look like this:

    A B C F
    1 mat 1 mat 2 mat 3 mat 4
    2 equip 1 equip 2 equip 3 equip 6
    3 equip Info equip Info equip Info equip Info
    equip 4
    equip Info
    equip 5
    equip Info

    See the attached file for better depiction of data. Here is the code I have so far

    Code:
    Sub FormatFromPivotToANRR()
    
        Dim c
        For Each c In Range("A1:DPT1").Cells
            If c = "" Then
                c.Select
                ActiveCell.Offset(1, 0).Range("A1:A2").Select
                Selection.Copy
                ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
                Selection.End(xlDown).Offset(1, 0).Select
                ActiveSheet.Paste
                ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
                Application.CutCopyMode = False
                Selection.Delete Shift:=xlToLeft
                End If
                 Next
    End Sub
    End Code:

    Thanks in advance.
    Attached Files Attached Files
    Last edited by rylo; 12-20-2010 at 08:31 PM. Reason: table format didnt work

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1