+ Reply to Thread
Results 1 to 3 of 3

Deleting blank rows

  1. #1
    Michael Chang
    Guest

    Deleting blank rows

    Hi,

    I am writing a macro to delete the entire row if all the cells are blank.
    For example, in a range which 10 rows by 3 columns. Data in the first column
    is 2 rows deep, 2nd column - 1 row, 3rd column - 3 rows. I want the macro
    to delete rows 4 through to 10.

    This is what I tried:

    Dim c As Range
    Dim myString As String

    For Each c In Range("A1:A10").Cells
    myString = c & c.Offset(0, 1) & c.Offset(0, 2)
    If Len(myString) = 0 Then
    c.EntireRow.Delete
    End If
    Next c

    What happens is that only half the blank rows are deleted. It seems as
    though after the first row is deleted 'Next c' skips a row. What am I doing
    wrong?

    Regards,
    Michael



  2. #2
    Tom Ogilvy
    Guest

    Re: Deleting blank rows

    Dim c As Range
    Dim myString As String
    Dim i as Long

    For i = 10 to 1 step -1
    set c = Cells(i,1)
    myString = c & c.Offset(0, 1) & c.Offset(0, 2)
    If Len(myString) = 0 Then
    c.EntireRow.Delete
    End If
    Next c

    --
    Regards,
    Tom Ogilvy

    "Michael Chang" <anonymous@discussions.microsoft.com> wrote in message
    news:OzI2941$EHA.600@TK2MSFTNGP09.phx.gbl...
    > Hi,
    >
    > I am writing a macro to delete the entire row if all the cells are blank.
    > For example, in a range which 10 rows by 3 columns. Data in the first

    column
    > is 2 rows deep, 2nd column - 1 row, 3rd column - 3 rows. I want the macro
    > to delete rows 4 through to 10.
    >
    > This is what I tried:
    >
    > Dim c As Range
    > Dim myString As String
    >
    > For Each c In Range("A1:A10").Cells
    > myString = c & c.Offset(0, 1) & c.Offset(0, 2)
    > If Len(myString) = 0 Then
    > c.EntireRow.Delete
    > End If
    > Next c
    >
    > What happens is that only half the blank rows are deleted. It seems as
    > though after the first row is deleted 'Next c' skips a row. What am I

    doing
    > wrong?
    >
    > Regards,
    > Michael
    >
    >




  3. #3
    Mike Fogleman
    Guest

    Re: Deleting blank rows

    Delete from the bottom, up.

    Mike F
    "Michael Chang" <anonymous@discussions.microsoft.com> wrote in message
    news:OzI2941$EHA.600@TK2MSFTNGP09.phx.gbl...
    > Hi,
    >
    > I am writing a macro to delete the entire row if all the cells are blank.
    > For example, in a range which 10 rows by 3 columns. Data in the first

    column
    > is 2 rows deep, 2nd column - 1 row, 3rd column - 3 rows. I want the macro
    > to delete rows 4 through to 10.
    >
    > This is what I tried:
    >
    > Dim c As Range
    > Dim myString As String
    >
    > For Each c In Range("A1:A10").Cells
    > myString = c & c.Offset(0, 1) & c.Offset(0, 2)
    > If Len(myString) = 0 Then
    > c.EntireRow.Delete
    > End If
    > Next c
    >
    > What happens is that only half the blank rows are deleted. It seems as
    > though after the first row is deleted 'Next c' skips a row. What am I

    doing
    > wrong?
    >
    > Regards,
    > Michael
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1