Excel Formula Question

[Mjsherwin]

Hello,

I'm trying to get a formula (so that there is no need for manual intervention on the part of the user) to format the below example that is contained in 1 Column of data.

What I currently have:

A
B
(Blank Cell)
C
D

What I'm getting:

A
B
C
C
D

What I want to get:

A
B
C
D
(Blank Cell)

Essentially, if the cell is blank then I want the value returned to be the next cell below it unless there are 2 blank cells & then just return a blank. Also, the data points cannot repeat themselves. If anyone could point me in the right direction I'd greatly appreciate it. Thank you

 

[Ezguy4u]

Mjsherwin let's get the ball rolling. Now this VBA program is really basic. Maybe some of the A Students will weigh in and come up with a 2 line program. But until then, this should get the conversation going. Also this should generate more questions than answers but we have to start some place.

Sub Delete2()

Dim LastRow As Long
Dim Steps As Long
Dim Trap As Boolean

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(LastRow, 1).Select

Trap = True
For Steps = LastRow To 1 Step -1

Cells(Steps, 1).Select
If ActiveCell <> "" And Trap = False Then
Trap = True
End If

If ActiveCell = "" And Trap = True Then
Rows(Steps).Delete
Trap = False
End If

If ActiveCell = ActiveCell.Offset(1, 0) Then
Rows(Steps).Delete
End If

Next Steps
End Sub

So here is an example of the data.

delete 1.xlsm
	A
1	A
2	B
3
4	C
5	D
6
7
8	E
9	F
10	G
11	G
12
13
14	H
15	I
16
17	J
Delete row

 

[Michael M]

If it's only 1 column of data ....How about this

• Highlight your column
• Press F5 to bring the "Go to" dialog.
• In the dialog box click the Special… button.
• In the "Go to special" dialog, choose "Blanks" radio button and click OK.
• Right-click on any selected cell and select "Delete…".
• In the "Delete" dialog box, choose "Entire row" and click Entire row.

3 ways to remove blank rows in Excel - quick tip

3 quick and correct ways to remove blank rows without destroying your data. All solutions work in Excel 2021, 2019 and lower.

www.ablebits.com

 

[Peter_SSs]

I'm trying to get a formula... to format the below example that is contained in 1 Column of data.

Welcome to the MrExcel board!

Do you mean like this?

21 07 25.xlsm
	A	B
1	A	A
2	B	B
3		C
4	C	D
5	D
6
Remove blanks

Cell Formulas
Range	Formula
B1:B4	B1	=FILTER(A1:A10,A1:A10<>"","")
Dynamic array formulas.