Delete blank cell to the right

Hi,

The following codes selected range H1:J13 -

Sub test()
'created by NORMAN JONES: http://www.44342.com/MS-EXCEL-f84-t68546-p1.htm
Cells(1, Columns.Count).End(xlToLeft)(1, 1).Select
End Sub

Sub sce()
'CREATED BY PastorMike: http://www.excelbanter.com/showthread.php?t=297162
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

'keyboard shortcut: shift+ctrl+end: http://www.excelbanter.com/showthread.php?t=297162

'ps. sometimes code select more than last non-blank cell... :(

End Sub

Sub whole()
Call test
Call sce
End Sub

then I want to (remove blank cells, and) shift non-blank cells to the right (range H1:J13);
below code shift non-blank cells to left:

Sub DeleteEmptyCells()
Selection.SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
End Sub

How it will look a code to shift cells to the right?

ps. see the attach

Hi Markos..

Can you please check, if below code is doing your purpose..

Sub test()
    Columns("J:J").Cut
    Columns("H:H").Insert Shift:=xlToRight
    Columns("J:J").Cut
    Columns("I:I").Insert Shift:=xlToRight
End Sub

thanks Debraj Roy, but this 3 columns is only an example (I have many columns);
JOHN H. DAVIS, your code works good, but something is wrong, see the results after run your code, looks like... (see the next attach)

ps. in line

For Each cell In Range("H1:H13")

I change to

For Each cell In Selection

ps. marco doesn't work to more than 3 columns...

Hi Markos..

Try this..

Sub SwapColumn()
Dim SwapRange As Range
Set SwapRange = Range("H:K")
With SwapRange
For i = 1 To .Columns.Count - 1
    .Columns(1).Cut
    .Columns(.Columns.Count + 1).Insert -4161
    Application.CutCopyMode = False
Next i
End With
End Sub

change color are to set your range

thanks Debraj Roy,
but... your code do the same as below code:

Sub Fliphorizontally()
    Dim vTop As Variant
    Dim vEnd As Variant
    Dim iStart As Integer
    Dim iEnd As Integer
        Application.ScreenUpdating = False
        iStart = 1
        iEnd = Selection.Columns.Count
        Do While iStart < iEnd
            vTop = Selection.Columns(iStart)
            vEnd = Selection.Columns(iEnd)
            Selection.Columns(iEnd) = vTop
            Selection.Columns(iStart) = vEnd
            iStart = iStart + 1
            iEnd = iEnd - 1
        Loop
        Application.ScreenUpdating = True
End Sub

both codes only reverse horizontally the range, changing cells order...

codes related to yours: http://stackoverflow.com/questions/9...with-excel-vba

Sample sheet with expected output.. we cant run macro on JPG..

HI Markos...

Sub MoveSpaceToRight()
With Sheets("Arkusz1").Range("M3:AB40")
    For i = 1 To .Rows.Count
    On Error Resume Next
        .Range(Cells(i, 1), Cells(i, .Columns.Count)).SpecialCells(4).Cut
        .Cells(i, 1).Insert -4161
    On Error GoTo 0
    Next i
End With
End Sub

With a single error.. If no special cells found, still its inserting a single..

@ Other:
Can someone help me , how to catch, if no Specialcells BLANKS found

sorry , can't use "With selection" ,- because the code work incorrectly...

your code work great(!) but:
we must extended our range
by one empty column (to the right direction)!
(from the range M3:AB39 to the range M3:AC39,)

I did it!,- but someone know what's wrong with this code?!

see the next attach with a correct solution...

ps. in sheet2 (Arkusz2) incorrectly solution (!), let me explain:
if we use in our sheet the code first time,
in the upper right corner,
in first row, the last cell is blank
(in this case AC3 is blank!)
,but when we clear the date, and run second time our code,
the results will be perfect every time!
even after the file is closed and re-open it!

in sheet1 (Arkusz1) the data before running our code...