Hey, you all!
I wrote this macro myself, so I can only assume it is full of inefficiencies, but can you help me improve it?
It simply aligns an entire table to the right by copy pasting the values of the cells. Unfortunately the looping through the "if" statement makes it super slow, which looks to me like a rather easy task (check if empty, if not -> copy-paste, if yes ->next)
What am I missing here? Can anyone think of a faster way to do this? Funnily enough, I have been using this macro for a few months and I can swear that it used to be faster at the beginning! Either my computer has gone slower or I am getting nuts, neither option particularly exciting.
![]()
a b c 1 2 a b c 1 2 k l m 3 4 5 6 7 k l m 3 4 5 6 7 x y z 8 becomes x y z 8
![]()
Sub Allign_Right() Dim StartColumn As Integer Dim EndColumn As Integer Dim StartRow As Integer Dim EndRow As Integer Dim Row As Integer Dim Column As Integer Dim MaxColumn As Integer Dim MaxColumnVar As Integer Dim MaxColumnCount As Integer StartColumn = 2 EndColumn = 30 StartRow = 1 EndRow = 30 For Row = StartRow To EndRow With ActiveSheet MaxColumnVar = .Cells(Row, .Columns.Count).End(xlToLeft).Column End With If MaxColumnVar > MaxColumn Then MaxColumn = MaxColumnVar End If Next Row For Row = StartRow To EndRow MaxColumnCount = 0 ' Should be always 0 at start. For Column = MaxColumn To StartColumn Step -1 Cells(Row, Column).Activate If ActiveCell.Value <> "" Then Cells(Row, MaxColumn + 1 - MaxColumnCount).Value = ActiveCell.Value Cells(Row, Column).Clear MaxColumnCount = MaxColumnCount + 1 Else End If Next Column Next Row Cells(1, 1).Activate End Sub











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks