removing lines based on a certain criteria

**im_really_mad** · 03-15-2012, 04:36 PM

hey everyone, so i have a long list of addresses listed downwards in a column, each address (which takes 4 rows) is separated by multiple rows, and I need the addresses separated by only one row.

Basically, at the end I want all the addresses organized by row (so i have a address on 1 row and 4 columns, currently I have it as 4 rows and 1 column). Thats not the hard part because I have a macro that automatically copies and transposes it across rows. However, my macro transposes rows based on a constant amount of rows that I set. If i can remove all the extra rows in between addresses then I can set my macro to transpose every 5 rows so I can get the addresses to go across rather than downwards.

**haru** · 03-15-2012, 07:31 PM

When creating your macro, if you click "Use Relative Reference". You could have it move the first address to rows, then delete the now empty rows. Set a keyboard shortcut to that macro, and all you would have to do is repeat using the keyboard shortcut until you are done.

"Use Relative Reference" will ultimately start out where your selected cell is, not a consistent cell. if you post an example I can setup the macro for you.

**im_really_mad** · 03-16-2012, 09:21 AM

here is my macro

PHP Code:


Sub test() 
Dim rng As Range, m As Integer, c As Range 
Columns("c:E").delete 
m = 7 
Set rng = Range(Range("a2"), Range("a2").End(xlDown)) 
Set c = Range("a2") 
Do While c <> "" 
'MsgBox c.Address 
 
Range(c, c.Offset(m - 1, 0)).Copy 
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True 
Set c = c.Offset(m, 0) 
Loop 
 
 
End Sub

I have addresses set up like these

11111
11111
11111
11111

2222
2222
2222
2222
....

and i have around 2000 addresses

thanks for helping

**haru** · 03-16-2012, 04:22 PM

Assuming that you already started converting the 4 cell going to to be horizontal and there are empy rows to the next address.

I'm also assuming the cell look like this, though it doesn't matter what's in the 4 cells.

123 street
City
State
Zip

it will convert to this (in separate cells):

123 street City State Zip

To start this macro, select the first blank cell under one of your converted addresses.
Run macro, it will convert one address at a time, just keep running it until you've converted them all.

PHP Code:


    Selection.End(xlDown).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Cut 
    Selection.End(xlUp).Select 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Cut 
    ActiveCell.Offset(-1, 1).Range("A1").Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Cut 
    ActiveCell.Offset(-1, 1).Range("A1").Select 
    ActiveSheet.Paste 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    Selection.Cut 
    ActiveCell.Offset(-1, 1).Range("A1").Select 
    ActiveSheet.Paste 
    Selection.End(xlToLeft).Select 
    ActiveCell.Offset(1, 0).Range("A1").Select