ExcelBanter - Aligning lists

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Aligning lists (https://www.excelbanter.com/excel-programming/296080-aligning-lists.html)

Hello

I have been trying to get a macro to align lists for me but i am
having difficulty. The lists would look like this

col A B C D
b01 b01 DR1
b02 b02 DR5
b03 b04 DR6
b04 b05 DR2
b05 b06 DR33
b06 b08 DR54
b07
b08

Basically column A is outputted to a database which brings back the
columns B and C for any record where the value of field A is found. In
the example the identifier b03 is not in the database so nothing is
brought back and therefore the remaining records are shifted up. I
would like the data to end up as below

col A B C D
b01 b01 DR1
b02 b02 DR5
b03
b04 b04 DR6
b05 b05 DR2
b06 b06 DR33
b07
b08 b08 DR54

The only way i can do this is mannually using copy and paste, any
ideas how i can get a macro to do this?

Regards

Richard

Hi Richard

Something like this would work for you

Sub AllignRows()
Dim Rng As Range
Dim Lastrow As Single

Lastrow = Range("A" & Rows.Count).End(xlUp).Row

Set Rng = Range("A1:A" & Lastrow)

For Each c In Rng
If c.Value < c.Offset(0, 1).Value Then
c.EntireRow.Insert
c.Offset(-1, 0).Delete
End If
Next c

End Sub

HTH

Davi

--
Message posted from http://www.ExcelForum.com

Hi Richard,

Here is a macro

Sub Align()
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value < Cells(i, "B").Value Then
Range("B" & i & ":C" & i).Insert Shift:=xlDown
End If
Next i
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Richard" wrote in message
om...
Hello

I have been trying to get a macro to align lists for me but i am
having difficulty. The lists would look like this

col A B C D
b01 b01 DR1
b02 b02 DR5
b03 b04 DR6
b04 b05 DR2
b05 b06 DR33
b06 b08 DR54
b07
b08

Basically column A is outputted to a database which brings back the
columns B and C for any record where the value of field A is found. In
the example the identifier b03 is not in the database so nothing is
brought back and therefore the remaining records are shifted up. I
would like the data to end up as below

col A B C D
b01 b01 DR1
b02 b02 DR5
b03
b04 b04 DR6
b05 b05 DR2
b06 b06 DR33
b07
b08 b08 DR54

The only way i can do this is mannually using copy and paste, any
ideas how i can get a macro to do this?

Regards

Richard

Richard,

if both columns A & B are returned in sequential order,
then something like this (untested) code should work:

Sub ReAlign()

Dim LastRow as integer
Dim X as Integer

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For X=1 to lastrow

If cells(x,2).value < cells(x,1).value) then
range(cells(x,2),cells(Lastrow,3)).cut destination:= _
Cells(X+1,2)

End if

Next X

End Sub

'Cheers, Pete.
-----Original Message-----
Hello

I have been trying to get a macro to align lists for me
but i am
having difficulty. The lists would look like this

col A B C D
b01 b01 DR1
b02 b02 DR5
b03 b04 DR6
b04 b05 DR2
b05 b06 DR33
b06 b08 DR54
b07
b08

Basically column A is outputted to a database which
brings back the
columns B and C for any record where the value of field A
is found. In
the example the identifier b03 is not in the database so
nothing is
brought back and therefore the remaining records are
shifted up. I
would like the data to end up as below

col A B C D
b01 b01 DR1
b02 b02 DR5
b03
b04 b04 DR6
b05 b05 DR2
b06 b06 DR33
b07
b08 b08 DR54

The only way i can do this is mannually using copy and
paste, any
ideas how i can get a macro to do this?

Regards

Richard
.