New to Macros

exdter · #1 (**permalink**) 08-13-04, 09:57

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.

DavidCoutts · #2 (**permalink**) 08-13-04, 10:54

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

exdter · #3 (**permalink**) 08-13-04, 11:27

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.

DavidCoutts · #4 (**permalink**) 08-13-04, 11:30

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

exdter · #5 (**permalink**) 08-13-04, 11:55

Thank you VERY MUCH!!! That is exactly what I needed!!!
Thanks again!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech