Excel Move Cells to Right.

[Solved/Closed]
Report
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012
-
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012
-
Hello,

I have a task to move several cells in a few column to the far right column (like below)
Number of column can be up to 25.

Your help will be greatly appreciated.

http://www.editgrid.com/user/j3kj3k/ExcelQuestion

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this

Sub MoveData()
Dim lRow As Long
Dim iMaxCol As Integer
Dim lMaxRow As Long
Dim iCol As Integer
Dim Cell As Range
Dim sValue As Variant

    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set Cell = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then Exit Sub
    
    For lRow = 2 To Cell.Row
    
        For iCol = iMaxCol To 2 Step -1
            
            If Cells(lRow, iCol) = "" Then
                sValue = Cells(lRow, iCol).End(xlToLeft)
                Cells(lRow, iCol).End(xlToLeft) = ""
                Cells(lRow, iCol) = sValue
            End If
            
        
        Next iCol
  
  Next lRow

    
End Sub
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012

Many Thanks !!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
On what basis would column shift. In your example some did and some did not.
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012

Hi, Rizvisa1

That was my bad. I have updated the spread sheet. Basically move all none balck cell next to last column (not concatenate).

Thanks