+ Reply to Thread
Results 1 to 5 of 5

Delete empty rows in range after last used row

Hybrid View

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Delete empty rows in range after last used row

    Hi,

    I'm struggelig to find out how to delete empty rows in range after last used row.
    I have a tried it in the current example file.

    As you can see there's a list with values. In between some of the lines there are empty ones. They have to stay there. I only want to delete the ones after the last used row.

    Could someone help me? This is a part of a bigger file so the range has to be set.

    I've tried
    For Counter = 2 To 90
            curCell = Range("A" & Counter).Select
            NextCell = Range("A" & Counter - 2).Value
            If NextCell = "" Then Selection.EntireRow.Delete
        Next Counter
    It works, somewhat but alot of lines are still there.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Delete empty rows in range after last used row

    what do you mean for last used row ? the row with TOTAL or the row with T ?
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Delete empty rows in range after last used row

    Hi, crakter,

    if you delete the rows Excel will fill them up again. Maybe you are looking for something like this:
    Sub HideRange()
    Range(Range("A" & Rows.Count).End(xlUp).Offset(1, 0), Range("A" & Rows.Count)).EntireRow.Hidden = True
    End Sub
    And to make the range visible again:
    Sub ShowRange()
    Columns(1).EntireRow.Hidden = False
    End Sub
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Delete empty rows in range after last used row

    Sub emptyme()
    With ActiveSheet
     .Range("A1", Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(4).EntireRow.Delete
    End With
    End Sub

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Delete empty rows in range after last used row

    Hi,

    Thank you for that HaHoBe. That did the trick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA: Delete all empty Rows
    By bamaisgreat in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-10-2013, 04:16 PM
  2. Delete empty rows
    By si14 in forum Excel General
    Replies: 1
    Last Post: 05-30-2012, 08:55 AM
  3. delete duplicate rows, delete empty rows
    By loade in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-03-2012, 05:42 AM
  4. Autofilter/delete empty rows is deleting non-empty rows!
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2010, 12:31 PM
  5. Delete Empty Rows
    By pegbol in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2005, 12:14 AM

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