How to move data left if blank

[keranali]

Hi Guys J2 to M2 has data in a row I want to be able to move data from the right to the left within those columns if the left cell is empty. How possible is this.

EXAMPLE

Excel Workbook
	J	K	L	M
2	abcd	defg		hijk
3	abcd	defg	hijk
Sheet3

Thanks
K

 

[Jonmo1]

Highlight the entire dataset you want to do this to

Click Edit - Goto - Special - Blanks - OK
Right Click - Delte - Shift Left - OK


Hope that helps.

 

[keranali]

Thanks for your reply however I want to assign the command to a button do you know of a vba that can complete this.

Thanks

 

[Jonmo1]

Tha macro recorder is your friend...

Tools - Macro - Record New Macro
Give it a name and click OK
Do the steps described above
Click Tols - Macro - Stop Recording.


Hope that helps.

 

[keranali]

Hi ok it worked so far however I only want it to work in between Col J to M, the macro is moving the items to the extreme left col A can this be fixed.


Sub Macro1()
'
' Macro1 Macro
'

'
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft
End Sub


Thanks
k

 

[Jonmo1]

You would then only select column J to M at the beginning...

Try

Range("J:M").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

 

[keranali]

Ok I have another separate macro in the same sheet that runs all the time i don't know if it is affecting it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long


LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:M" & LastRw)) Is Nothing Then
Range("O6:N8,O10:O15,R10:R15").ClearContents
Else
ThisRw = ActiveCell.Row
Range("O6:O8").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 3).Value)
Range("O10:O13").Value = Application.Transpose(Cells(ThisRw, "E").Resize(, 4).Value)
Range("R10:R13").Value = Application.Transpose(Cells(ThisRw, "J").Resize(, 4).Value)
Range("O15").Value = Application.Transpose(Cells(ThisRw, "I").Resize(, 1).Value)
End If

End Sub



I keep getting "Runtime error '1004,:
This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet.

Thanks
K

 

[Jonmo1]

Yep,

Try

Application.EnableEvents = False
Range("J:M").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
Application.EnableEvents = True

 

[keranali]

Thanks for taking the time in helping me so far I applied the code


Sub Macro1()
'
' Macro1 Macro
'

'
Application.EnableEvents = False
Range("J:M").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
Application.EnableEvents = True

End Sub


However I am still getting the error "1004"

Is there something i am missing

Thanks
K

 

[Jonmo1]

I believe the text version of that error is "No Cells Found"

That pretty much means that no blank cells were found.


If you have blanks, they are likely not TRUELY Blank.
Are the values in J:M Formulas?
Or Used to be formulas, that you have since copy/pastespecial/values?