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

 

[keranali]

Hi no formulas have been used can I send a copy to you I know the VBA is working its just not applying to this sheet.

K

 

[Jonmo1]

So it works on other sheets, just not this particular one?

Try

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

 

[keranali]

Same thing but I takes about 10 secs before the error shows when I click debug I am getting this line with the error.

.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

Thanks
K

 

[Jonmo1]

Well, the error is still pretty self explainatory..

"No Cells Found" (This is the error right?)


That means there are no blank cells in the range J:M

If you see there are blanks in the range, pick one and note it's address (J20 or whatever).

What do these formulas return

=LEN(J20)
=CODE(J20)
=ISBLANK(J20)
=ISNUMBER(J20)
=ISTEXT(J20)

Where J20 is a cell you believe to be blank.

 

[keranali]

Hi in 2006 I saw you had some input with a similar vba with eric van geit with the code as follows:

Sub fill_blanks()
'Erik Van Geit
'061109

'fill empty cells in column B with values of column A
'ASSUMPTIONS
'only values in column B (formulas will be converted)

Dim LR As Long
Dim rng As Range
Dim blanks As Range

LR = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("B1:B" & LR)

On Error Resume Next
Set blanks = rng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If blanks Is Nothing Then Exit Sub

'you cannot use blanks.Value = blanks.Value
'all cells would get the first value if blanks are not adjacent
blanks.FormulaR1C1 = "=RC[-1]"
rng.Value = rng.Value

End Sub

Can this code apply to my situation....

thanks
K

 

[Jonmo1]

OK, so you want to fill the blanks with whatever the value is to the right (or left) of the blank?

Try

With Range("J:M")
    .FormulaR1C1 = "=RC[-1]
End With

That will fill blanks with the value to the left.

Change -1 to 1 to make it fill with values to the right.

 

[keranali]

I dont know if I am going right but this is what i got

Excel Workbook
	J	K	L	M
2
3	868-623-1322			868-680-0000
4
5
6	0
7	#VALUE!
8	TRUE
9	FALSE
10	FALSE
Sheet1

 

[keranali]

Hi that formula takes information from I and populates all the cells till M this is what i am thinking of

Excel Workbook
	J	K	L	M
2	ABCDE		EFGHI	JKLM
3		NOPQR	STUV
4				WXYZ
5
6	ABCDE	EFGHI	JKLM
7	NOPQR	STUV	WXYZ
Sheet1

I believe something is wrong somewhere because I can't cut and paste values within the workbook do you know why is this like this.

 

[keranali]

I believe first VBA is locking the copy and paste as well as right click cell to delete I am wondering if this is affecting.

Thanks
K

 

[keranali]

Hi any luck I am still trying

Thanks
K