Deleting Blank Cells

damian · Apr 22nd, 2002, 05:17 PM

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 · Apr 22nd, 2002, 05:39 PM

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---

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 22nd, 2002, 05:17 PM	#1
damian New Member Join Date: Apr 2002 Posts: 1	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!

Apr 22nd, 2002, 05:39 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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---