Excel Move Cells to Right.

Solved/Closed
j3kj3k
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012
- Jun 15, 2010 at 05:25 PM
j3kj3k
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012
- Jun 17, 2010 at 12:04 PM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 16, 2010 at 08:20 PM
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
j3kj3k
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012

Jun 17, 2010 at 12:04 PM
Many Thanks !!!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 15, 2010 at 07:52 PM
On what basis would column shift. In your example some did and some did not.
0
j3kj3k
Posts
16
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
July 11, 2012

Jun 16, 2010 at 01:21 PM
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
0