Remove blank cell of rows and shift the rest to left

[mehrana]

I have a table with 60+ columns. More data is periodically added (using Microsoft form) to this table. There are / will be many blank cells in each row (80% of the entire cells). Instead of this very wide table, I need to display only the data on a screen without the blank cells. The screen can of any kind, such as another table with the blank cells removed and data cells are shifted to left. It will be great if this can done automatically each time when a new row is added or more data is added to cells (on left side of the existing).

I appreciate any help you can provide.

 

[mehrana]

Please see the images below.

#1 existing data > #2 how to display the date

 

[mehrana]

Correction: more data is added to cells (on right side of the existing) >> the cells are blank.

 

[dermie_72]

you could try this code. Recommend you try it on dummy data first though just to be safe.

Sub mehrana()

Dim LR As Long, LC As Long
Dim Starttime As Double, Secondselapsed As Double


Starttime = Timer
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False

LR = Cells(Rows.count, "A").End(xlUp).Row
LC = Cells(1, Columns.count).End(xlToLeft).Column + 1
For C = LC To 1 Step -1
    For R = LR To 1 Step -1
        If Cells(R, C).Value = "" Then
            Cells(R, C).Delete shift:=xlToLeft
            Else
        End If
    Next R
Next C
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Secondselapsed = Round(Timer - Starttime, 2)
MsgBox "This code ran successfully in " & Secondselapsed & " seconds", vbInformation
End Sub

 

[mehrana]

Thank you very much for the macro. As you suggested, I will try in a copy of the spreadsheet. I need to learn how to add a macro first!

 

[felixstraube]

Another option with a formula:

Would this work? Columns A:H is Table1, which could be in another sheet than the formula.

Book6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8
2	A	ab	777			a	b	c		A	ab	777	a	b	c
3	B	bc	1	2	77			88		B	bc	1	2	77	88
4	C	de	a	b			c	e		C	de	a	b	c	e
5	D	eg				1				D	eg	1
6	E	kk		sd			dsaf			E	kk	sd	dsaf
7	d	de	e							d	de	e
8	b		a			adf				b	a	adf
9	a									a
10
Sheet6

Cell Formulas
Range	Formula
J2:P9	J2	=LET(t, Table1, tj, BYROW(t, LAMBDA(x, TEXTJOIN("|", 1, x)&"|")), res, DROP(REDUCE("", tj, LAMBDA(arr,new, VSTACK(arr, TEXTSPLIT(new, "|")))), 1), IFERROR(res, "") )
Dynamic array formulas.

 

[mehrana]

Yes, thank you. This is exactly what I need!
Can I automate the formula to run each time a new row is added and/or when new data is added to one or more existing blank cell(s) in the range (on right side)?

 

[mehrana]

you could try this code. Recommend you try it on dummy data first though just to be safe.

Sub mehrana()

Dim LR As Long, LC As Long
Dim Starttime As Double, Secondselapsed As Double


Starttime = Timer
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False

LR = Cells(Rows.count, "A").End(xlUp).Row
LC = Cells(1, Columns.count).End(xlToLeft).Column + 1
For C = LC To 1 Step -1
    For R = LR To 1 Step -1
        If Cells(R, C).Value = "" Then
            Cells(R, C).Delete shift:=xlToLeft
            Else
        End If
    Next R
Next C
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Secondselapsed = Round(Timer - Starttime, 2)
MsgBox "This code ran successfully in " & Secondselapsed & " seconds", vbInformation
End Sub

This Macro did not work. It deleted any column that had 1 or more blank cell, and shifted columns will all filled cells to left

 

[mehrana]

Another option with a formula:

Would this work? Columns A:H is Table1, which could be in another sheet than the formula.

Book6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8
2	A	ab	777			a	b	c		A	ab	777	a	b	c
3	B	bc	1	2	77			88		B	bc	1	2	77	88
4	C	de	a	b			c	e		C	de	a	b	c	e
5	D	eg				1				D	eg	1
6	E	kk		sd			dsaf			E	kk	sd	dsaf
7	d	de	e							d	de	e
8	b		a			adf				b	a	adf
9	a									a
10
Sheet6

Cell Formulas
Range	Formula
J2:P9	J2	=LET(t, Table1, tj, BYROW(t, LAMBDA(x, TEXTJOIN("|", 1, x)&"|")), res, DROP(REDUCE("", tj, LAMBDA(arr,new, VSTACK(arr, TEXTSPLIT(new, "|")))), 1), IFERROR(res, "") )
Dynamic array formulas.

Another option with a formula:

Would this work? Columns A:H is Table1, which could be in another sheet than the formula.

Book6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8
2	A	ab	777			a	b	c		A	ab	777	a	b	c
3	B	bc	1	2	77			88		B	bc	1	2	77	88
4	C	de	a	b			c	e		C	de	a	b	c	e
5	D	eg				1				D	eg	1
6	E	kk		sd			dsaf			E	kk	sd	dsaf
7	d	de	e							d	de	e
8	b		a			adf				b	a	adf
9	a									a
10
Sheet6

Cell Formulas
Range	Formula
J2:P9	J2	=LET(t, Table1, tj, BYROW(t, LAMBDA(x, TEXTJOIN("|", 1, x)&"|")), res, DROP(REDUCE("", tj, LAMBDA(arr,new, VSTACK(arr, TEXTSPLIT(new, "|")))), 1), IFERROR(res, "") )
Dynamic array formulas.

Yes, thank you. This is exactly what I need!

I tried it a few times, not sure exactly to make it work.

Can I automate the formula to run each time a new row is added and/or when new data is added to one or more existing blank cell(s) in the range (on right side)?

 

[felixstraube]

Yes, thank you. This is exactly what I need!
Can I automate the formula to run each time a new row is added and/or when new data is added to one or more existing blank cell(s) in the range (on right side)?

It will update automatically every time the table changes.