Inserting and Deleting Rows
Results 1 to 9 of 9

Thread: Inserting and Deleting Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Inserting and Deleting Rows

    I get a daily spreadsheet from someone else and they always insert double rows (ie.. Row 1 will have info on it and then they skip Row 2 and type info on Row 3).
    How can I delete these empty rows without having to go through the spreadsheet and individually delete each one?

  2. #2
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting and Deleting Rows

    Give this a go:

    Code:
    Public Sub DeleteBlankRows()
    Dim i   As Long, _
        LR  As Long
        
    LR = Range("A" & Rows.Count).End(xlUp).row
    Application.ScreenUpdating = False
    For i = LR To 1 Step -1
        If Range("A" & i).Value = "" Then
            Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,644
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Inserting and Deleting Rows

    cyng,

    You do not have to loop thru each cell and test if it is blank.


    Sample data before the macro:


    Sheet1

     ABCDE
    11111 
    2     
    33333 
    4     
    55555 
    6     
    77777 
    8     
    99999 
    10     
    1111111111 
    12     


    Excel tables to the web >> Excel Jeanie HTML 4




    After the macro:


    Sheet1

     ABCDE
    11111 
    23333 
    35555 
    47777 
    59999 
    611111111 
    7     
    8     
    9     
    10     
    11     
    12     


    Excel tables to the web >> Excel Jeanie HTML 4






    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Code:
    Option Explicit
    Sub DeleteOnlyBlankRows()
    Dim LR As Long
    Application.ScreenUpdating = False
    With ActiveSheet
      LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
      On Error Resume Next
      .Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      On Error GoTo 0
    End With
    Application.ScreenUpdating = True
    End Sub

    Then run the DeleteOnlyBlankRows macro.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    Jan 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Inserting and Deleting Rows

    Thank you both for your suggestions. I will try this when I get to work in the morning. I cannot believe it is this difficult just to delete unwanted rows in a spreadsheet. GEEZZZ!

  5. #5
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting and Deleting Rows

    If you don't have sub-total lines in the middle of your data, you can always just sort the data and the blank rows will go to the bottom

  6. #6
    New Member
    Join Date
    Jan 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Inserting and Deleting Rows

    Well I have removed all other data that did not pertain to the existing rows of information. You mean to tell me that just doing a simple sort will remove the blank rows to the bottom??

  7. #7
    Board Regular
    Join Date
    Jan 2011
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting and Deleting Rows

    You could data filter, select "blanks", delete them all, unfilter.

  8. #8
    New Member
    Join Date
    Jan 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Inserting and Deleting Rows

    Thank you all again. I will post tomorrow which was the easiest to take on for what should have been an easy spreadsheet.

  9. #9
    New Member
    Join Date
    Jan 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: Inserting and Deleting Rows

    Well DUH!!! It was that simple; select all - Sort A-Z to take out all the blank rows.
    When I get ready for another boneheaded question, I know where to look.

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
  •