1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Discussion in 'Business Applications' started by Gram123, Jan 26, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hello,
    I've long used a simple macro on various spreadsheets to reset the last used cell:

    Sub Reset_Range()
    Application.ActiveSheet.UsedRange
    End Sub

    This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

    I would like to add this stage into the macro.
    I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

    Option Explicit

    Sub DeleteBlankRows()
    Dim Rw As Long, RwCnt As Long, Rng As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    On Error GoTo Exits:
    If Selection.Rows.count > 1 Then
    Set Rng = Selection
    Else
    Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
    End If
    RwCnt = 0
    For Rw = Rng.Rows.count To 1 Step -1
    If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
    Rng.Rows(Rw).EntireRow.Delete
    RwCnt = RwCnt + 1
    End If
    Next Rw

    Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

    Sub DeleteBlankColumns()
    Dim Col As Long, ColCnt As Long, Rng As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    On Error GoTo Exits:

    If Selection.Columns.count > 1 Then
    Set Rng = Selection
    Else
    Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
    End If
    ColCnt = 0
    For Col = Rng.Columns.count To 1 Step -1
    If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then
    Rng.Columns(Col).EntireColumn.Delete
    ColCnt = ColCnt + 1
    End If
    Next Col

    Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

    However, it doesn't work with my current document - pressing CTRL + END after running the combined macro takes you to the same last cell as if you hadn't run it. Cells in the blank columns and rows at the end have formatting (shading, cell borders), but no obvious data.

    As a test, I also tried deleting the contents of 1 column in the middle of the data (selecting column and pressing Delete, just leaving the cell formatting in place). The macro failed to remove that column, too.

    So can someone help me get code that actually works in deleting empty columns and rows, and resetting the last used cell?

    Ta!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    The thing with Ctrl+End is that it will take you to the last cell every having contained data.
    What you need to do is have a macro that actually deletee all rows below the last filled row as well as all columns after the last filled column, after the workbook is saved then the Ctrl + End will identify he last row, columns correctly based upon the actual cell's contents.

    What you could do in your macro is add a line that saves the worksheet before you continue

    Activeworkbook.Save
    before the End Sub

    See if this makes a difference
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Just tested your code adding the Activeworkbook.Save in both subs and then Ctrl+End does it's job as expected.
     
  4. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi Keebellah.
    It still wasn't working for me, I tried a COUNTA formula on the empty cells, but it resulted in 0 (when I entered a space in one cell, it resulted in 1), so I couldn't see why it would work for you, but not for me.

    Then I figured out why - I was running the macro from a button on the toolbar, and it was still running just the Reset_Range Sub. I foolishly thought the macro would run everything in the module, but of course it doesn't (or at least, it doesn't if the part you refer to is the last thing in the module). (I'm not exactly a VBA expert!)
    So the seperate Subs for DeleteBlankRows and DeleteBlankColumns weren't actually being fired, despite being in the same Module.

    I fixed it by combining the 3 bits of code into a single Sub called Reset_Range.
    There were two instances of the delcared variable Rng = Range, so I dropped the latter, and there were two instances of Exits, so I changed them to ExitRows and ExitColumns, and then tested and it worked!


    So for all I didn't take your advice, I did play about with the placement of Activeworkbook.Save, thinking that I might be able to include it just once in the reset range section at the end, rather than once after deleting blank rows and once after deleting blank columns, and it was this that made me realise those 2 Subs weren't running, so thank you for your indirect help!

    Gram
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    You're welcome. :)
    Don't forget to use the 'Mark Solved' button
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1038197

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice