Microsoft Office Forums - View Single Post - [Solved] Right-Aligning Data to a Column

JBeaucaire · 02-01-2012, 09:46 AM

This macro will determine:

1) How many rows of data there are
2) How many columns of data there are
3) Cut the row of data and paste on same row so last column lines up

There can be no blank cells in the middle of data on a single row.

Code:

Sub AlignRight()
Dim LC As Long, LR As Long, Rw As Long, Cnt As Long

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LC = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

For Rw = 1 To LR
    Cnt = Rows(Rw).SpecialCells(xlConstants).Cells.Count
    Rows(Rw).SpecialCells(xlConstants).Cut Cells(Rw, LC - Cnt)
Next Rw

End Sub