CODEWITHSUNDEEP
excelexcel-formula
Shankar M G
Shankar M G

Reputation: 33

How to remove empty cells in excel?

I want to remove the empty cells in excel of thousands of rows and columns can anyone help me on this? The values of the excel should be in order.

Sample Input:

enter image description here

Desired Output:

enter image description here

Upvotes: 1

Views: 117

Answers (3)

JvdV
JvdV

Reputation: 75960

All you need to do is:

  • Select column A:B
  • Go to ribbon > Start
  • Under editing > Search & Select
  • Click > Select Special
  • Click > Blanks

Now all your empty cells should be selected

  • Hit Ctrl- and shift cells up.

Upvotes: 1

JMP
JMP

Reputation: 4467

This code is essentially the same as @Tony's, but it doesn't use any extra cells.

Dim counter As Integer, i As Integer
counter = 1
For i = 1 To 10
    If Cells(i, 1) <> "" Then
        Cells(counter, 1) = Cells(i, 1)
        counter = counter + 1
    End If
Next i
For i = counter To 10
    Cells(i, 1) = ""
Next i

There is no chance of losing data, as there are blanks in the original, so that counter is always less than or equal to i. The last for..next loop clears any remaining visible previous data.

Upvotes: 1

Tony Ringer
Tony Ringer

Reputation: 21

Press Alt F11 to run a script as a macro. This website offers some insight that may be helpful. https://www.excel-easy.com/vba/examples/delete-blank-cells.html

I hope this helps.

Upvotes: 1

Related Questions