Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Deleting Blank Cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •