Deleting Blank Cells

[damian]

I have a list of cells which I want to run a regression on. However, some of the data is missing, so I cannot run the regression. Is there a way for me to get excel to delete the blank cells.

Example:

Point Age Wage
1 10 12
2 30 40
3 28
4 50 49

So in this case, I would want to delete the entire 3rd row. I have 50,000 data points so I do no want to do it all manually.

Thanks!

 

[Jay Petrulis]

Hi Damian,

Try,

'---begin VBA---
Sub delete_rows()

Dim lastrow As Long
Dim rng As Range

Application.ScreenUpdating = False
Rows(1).Insert
Range("C1") = "temp"

With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Range("C1", Cells(lastrow, "C"))
rng.AutoFilter Field:=1, Criteria1:=""
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With

End Sub
'---end VBA---