Delete GAPS in columns

[sauvage]

To Whom it May Concern,

I have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaing it in words.

A1: 0.9
A2:
A3:
A4:
A5: -0.09
A6:
A7: 0.4

Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?

I would much appreciate your help as it would save me loads of time.

Kind Regards,

Dim

 

[sauvage]

That did work. Never knew it can be so easy. Thank you very much for your help.

Thanks to every one who tried to help I appreciate this.

Actually it worked on small sample and is not working on huge one. It deletes some of the gaps (84 rows) and leaves everything else( more than 2k). It says "No Cells Were Found" when I click OK.

Still need help

 

[sauvage]

Press ALT + F11, select Module from the Insert menu, paste in the code. Press ALT + Q, press ALT + F8 then double click btest

It has the same problem it deletes just the top. I think it is something to do with the fact that I have copied this column from my other excel file which had formulas in it whereas I copied as values only.

 

[jim may]

I originally saw that sauvage could have Data in MULTIPLE Columns so there is the need to test (using a Helper Column) If ALL eight columns are maybe DISPLAYING Blanks although there may be underlying formulas in the cells... Is this actually the case?

Jim

 

[Jonmo1]

It has the same problem it deletes just the top. I think it is something to do with the fact that I have copied this column from my other excel file which had formulas in it whereas I copied as values only.

Problem here is that a formula returning "" is NOT really blank, it's a Null Text string..

Here's an updated version of Vog's Code to handle that..

Sub btest()
With Columns("A")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

 

[jim may]

Sorta like the following (Sample)...

Excel Workbook
	D
4	TRUE
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D4	=NOT(COUNT(A4:C4))

 

[jim may]

Filter on Column D - TRUE -- Delete ALL

Excel Workbook
	A	B	C	D
1	Numbers1	Numbers2	Formula Col	Helper Col
2	355	277.00	0.78	FALSE
3	303	243.00		FALSE
4				TRUE
5	1	1.00	1.00	FALSE
6	277	416.00	1.50	FALSE
7	183	326.00	1.78	FALSE
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=B2/A2
C3	=IF(B3/A3<1,"",B3/A3)
C4	=IF(B3/A3<1,"",B3/A3)
C5	=B5/A5
C6	=B6/A6
C7	=B7/A7
D2	=NOT(COUNT(A2:C2))
D3	=NOT(COUNT(A3:C3))
D4	=NOT(COUNT(A4:C4))
D5	=NOT(COUNT(A5:C5))
D6	=NOT(COUNT(A6:C6))
D7	=NOT(COUNT(A7:C7))

 

[sauvage]

Problem here is that a formula returning "" is NOT really blank, it's a Null Text string..

Here's an updated version of Vog's Code to handle that..

Sub btest()
With Columns("A")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Yes, it worked well. I have made all adjustments to data. Thank you very much for helping out.