Results 1 to 5 of 5

Thread: New to Macros

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: New to Macros

    I have never done a macro before. I have a table in Excel that looks like:

    name1
    company1
    address1
    name2
    company2
    address2
    name3
    company3
    address3...

    I would like it to look like this:

    name1 company1
    name2 company2
    name3 company3...

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    how about this

    Code:
    Sub test()
    Dim I As Integer
    Dim Lastrow As Integer
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
        For I = 1 To Lastrow Step 3
            Range("A" & I & ":A" & I + 2).Copy
            Range("B" & I).PasteSpecial xlPasteAll, Transpose:=True
            Range("A" & I + 1 & ":A" & I + 2).Clear
        Next I
        
        Range("A1:A" & Lastrow).EntireRow.Sort Range("A1")
        Range("A1").EntireColumn.Delete
        
    End Sub

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    Thanks alot!!! That is amazing. The only problem is that my first column is sorted alphabetically and this throws off the whole table.

    Thanks again.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    i sorted alphabetically at the end as this gets rid of the spaces, you could get rid of this and delete out all the blankrows if you like replace
    Range("A1:A" & Lastrow).EntireRow.Sort Range("A1")

    with
    Range("A1:A" & Lastrow).specialcells(xlcelltypeblanks).entirerow. delete

    dave

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    Thank you VERY MUCH!!! That is exactly what I needed!!!
    Thanks again!!!!

Posting Permissions

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