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

Thread: Deleting rows not containing data. HOW?

  1. #1
    Guest

    Default

    I have a spreadsheet with 10 columns, the first of which contains TEXT, the other 9 to contain data if necessary, and 100 rows. The rows add across on each line and the columns add down.

    I want Excel to delete the rows that have NO digits in them. Would this be some kind of Formatting command?

    Help!!!

    Feel free to email me directly at
    rgardner@mdp.com

    and thank you.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ==

    Can you not autofilter and filter to BLANKS and delete the selection???

    This will do it.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    Guest

    Default

    I dunno.
    Can you explain Autofilter and Filter to Blanks to a novice Excel user? Please!

    Thank you

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 13:12, Anonymous wrote:
    I have a spreadsheet with 10 columns, the first of which contains TEXT, the other 9 to contain data if necessary, and 100 rows. The rows add across on each line and the columns add down.

    I want Excel to delete the rows that have NO digits in them. Would this be some kind of Formatting command?

    Help!!!

    Feel free to email me directly at
    rgardner@mdp.com

    and thank you.

    Sub deleteBlankRows()

    Range("A1").Select ' Select the starting cell
    For i = 1 To 100 ' Change 100 to whatever the last row you want to iterate to.

    If ActiveCell.Value Like 0 Then
    ActiveCell.EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(RowOffset:=1).Activate

    Else

    ActiveCell.Offset(RowOffset:=1).Select

    End If


    End Sub



    _________________
    Regards,

    Gary Hewitt-Long

    [ This Message was edited by: gplhl on 2002-02-27 13:47 ]

  5. #5
    Guest

    Default

    totally appreciated...however, still a little too complex for me. where do I type (?) all that???

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 13:57, Anonymous wrote:
    totally appreciated...however, still a little too complex for me. where do I type (?) all that???
    When you have your workook open, press Alt + F11.

    This will open your VBA editor.

    Right click on ThisWorkbook for the project of the workbook you want this macro in. Click on Add.. Module.

    Paste the code in the module, save and then you can run the macro from tools Macro.


    Regards,

    Gary Hewitt-Long

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Apologies to Gary if your VBA already interprets this and takes care of it...

    Anon :

    by "digits" do you just mean the numbers 1-9 or do you mean "anything" ie any numbers or characters, letters, signs etc etc ?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That'l teach me not to check code when I write it. The last one didn't work, I forgot to put Next i and used 0 (which I use in one of my own macros) instead of "" to look for empty cells.

    Sorry

    Working code below.


    Sub deleteBlankRows()

    Range("A1").Select ' Select the starting cell
    For i = 1 To 100 ' Change 100 to whatever the last row you want to iterate to.

    If ActiveCell.Value Like "" Then
    ActiveCell.EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(RowOffset:=1).Activate

    Else

    ActiveCell.Offset(RowOffset:=1).Select

    End If

    Next i
    Range("A1").Select

    End Sub
    Regards,

    Gary Hewitt-Long

  9. #9
    Guest

    Default

    I mean only NUMBERS. There will be text on each row, but if there aren't any numbers in any of the columns, I want the row deleted.

    Is that what that VB mocule will do???

  10. #10
    Guest

    Default

    Also, will GPLHL's code work as is if I don't want the first two columns included? (They both have text in them.) The next 8 have or potentially could have NUMBERS (which if they don't, I want the entire row deleted), followed by a last column with a formula to add across each row. (This formula will not interfere with the code, will it?)

    Thanks for all your help, so far.

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
  •