Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Further to deleting identical rows

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would also like something like this but mine would be in macro form. When I push the button I would like to delete all blank rows that are between lines of data.

    I'll give you an example:

    On some of the spreadsheets we have here, the users input rows of info, but when they start a new day they leave a row or two as a gap to signify a new day (makes it easier for quality checkers). I'd like to have a macro that removes these blank rows

    Any suggestions?

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assuming that you will always have data in column A, the following code should work for you.

    Sub del_row()

    Range("a1").Select

    Do Until ActiveCell.Row = Range("a65536").End(xlUp).Row + 1

    If ActiveCell.Value = "" Then

    ActiveCell.EntireRow.Delete (xlUp)

    Else

    ActiveCell.Offset(1, 0).Select

    End If

    Loop


    End Sub

  3. #3
    Guest

    Default

    On 2002-02-27 06:30, dognz wrote:
    Assuming that you will always have data in column A, the following code should work for you.

    Sub del_row()

    Range("a1").Select

    Do Until ActiveCell.Row = Range("a65536").End(xlUp).Row + 1

    If ActiveCell.Value = "" Then

    ActiveCell.EntireRow.Delete (xlUp)

    Else

    ActiveCell.Offset(1, 0).Select

    End If

    Loop


    End Sub
    This could result in some rows being skipped.

    Better to use :-

    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK I just tried that, unfortunately it deleted loads of rows that had info in them too. Some of the rows have a couple of things in them (IE: A1 contains a name and nothing else in the row, whereas A2 contains info in every cell up until X2)

    It's just the actual blank rows with nothing in any cell in all the row I want to get shot of.

    Oh and could it start at say row A4?
    I dont want much do I?

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    YEP - A LOT BETTER!

  6. #6
    Guest

    Default

    On 2002-02-27 06:37, Audiojoe wrote:
    OK I just tried that, unfortunately it deleted loads of rows that had info in them too. Some of the rows have a couple of things in them (IE: A1 contains a name and nothing else in the row, whereas A2 contains info in every cell up until X2)

    It's just the actual blank rows with nothing in any cell in all the row I want to get shot of.

    Oh and could it start at say row A4?
    I dont want much do I?

    Do you have any column where the cells always contain data except for the rows that are entirely blank?

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, there's a date column in column A that everyone fills out

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Follow this steps and RECORD a macro while you are doing them.

    Hit F5
    Type A65536.
    Hit Control Up
    Hit Control + and Choose "Entire Column"
    Press Shift Control Up
    Keep Shift Control Pressed and hit the Down arrow until you are in A4.
    Hit Enter
    Type this formula

    =IF(COUNTA(B4:IV4),1,"")

    Hit Control Enter

    Hit F5
    Click "Special"
    Select "Formulas" and leave only checked "Text"
    Hit Control -
    Select "Entire Row"
    Hit Control -
    Select "Entire Column"

    Stop recording the macro.

    How's that ?


    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Afraid not, no. It came up with a message saying it could not shift non-blank data??
    You see I have set up a macro that copies and pastes all the agents sheets and into one big worksheet and that's the one I wanted to clean up. Some of the cells the agent fills in are validated, I dont know if this has anything to do with it?

    If it's not possible then dont worry too much

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mhmm, maybe you have some merged cells ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

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
  •