Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2012, 06:03 AM
sunnyside sunnyside is offline Right-Aligning Data to a Column Windows Vista Right-Aligning Data to a Column Office 2007
Novice
Right-Aligning Data to a Column
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default Right-Aligning Data to a Column


Hi,
I have several rows of numbers with each row containing between 1 and 20 columns of numbers.
I'd like to "right-align" the rows of numbers so that the last number in each row are aligned with each other. In other words, alignment is right-to-left, not-to-right.

Example:
C1 C2 C3 C4 C5 C6 C7
R1 5 6 7 5 4 3
R2 3 2 3 1 4
R3 8 9
R4 2 1 2 6

I'd like 3, 4 9, 6 to align under C7 and their predecessors to align in the cells preceding behind them.

thank you,
sunnyside
Reply With Quote
  #2  
Old 02-01-2012, 09:46 AM
JBeaucaire JBeaucaire is offline Right-Aligning Data to a Column Windows XP Right-Aligning Data to a Column Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 02-11-2012, 12:08 PM
sunnyside sunnyside is offline Right-Aligning Data to a Column Windows Vista Right-Aligning Data to a Column Office 2007
Novice
Right-Aligning Data to a Column
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default RE: aligning columns

Thank you!
This worked great.

Sunnyside
Reply With Quote
  #4  
Old 02-27-2012, 01:25 AM
gsrikanth gsrikanth is offline Right-Aligning Data to a Column Windows XP Right-Aligning Data to a Column Office XP
Competent Performer
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by sunnyside View Post
Thank you!
This worked great.

Sunnyside
how to give range

i need to selected range

i select range from mouse

it need little change above script
Reply With Quote
Reply

Tags
right align data cells

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I change the horizontal scrollbar to scroll smoothly rather than column by column carpat Excel 0 01-10-2012 09:34 AM
Right-Aligning Data to a Column Need to search a column for a macth and return a result from a third column pdfaust Excel 2 02-03-2011 03:02 PM
Right-Aligning Data to a Column Aligning column text in a table dpeattie Word 6 12-29-2010 04:59 PM
Right-Aligning Data to a Column 2-Column Text Slides and Aligning Bullets KitKatJM PowerPoint 1 03-09-2010 06:45 PM
Inverting Data in a Column mrdave100 Word 0 03-08-2010 01:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft