+ Reply to Thread
Results 1 to 10 of 10

VBA Find last row and column that contain data and delete all blank rows and blank columns

  1. #1
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    VBA Find last row and column that contain data and delete all blank rows and blank columns

    I have a workbook that contains 7 tabs. Each tab has a different number of columns and rows filled with Data. For Example:

    Tab 1 Contains Range "A1:AB7500"
    Tab 2 Contains Range "A1:AK6300"
    Tab 3 Contains Range "A1:O5800"

    The row number is going to vary ALL THE TIME. I need a macro that does the following:

    - Finds the last row that contains data --> Delete all rows below this last row
    - Finds the last column that contains data --> Delete all columns to the right of the last column

    I need this macro to run through each tab and complete the same task (i.e. Go through each tab and delete all rows below the last row that contains data and delete all columns to the right of the last column that contains data.

    Please Help!! Thanks!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    It is my understanding that there is a blank row or at least a blank cell to mark the end of data right?

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    Please Login or Register  to view this content.
    Gary's Student

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    You can't delete (i.e. remove) those unused rows and columns from a sheet - all you can do is to hide them from view.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    Thanks jakobshavn,

    This appears to be exactly what I am looking for. However, how do I test the macro to ensure it is deleting?

  6. #6
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    I don't know.

  7. #7
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    This might render what you seeking...

    Place some data in other columns but in a range that you know beforehand has to be deleted. Run the macro. and if it's clean the macro worked! Place the data to right of the range and at the bottom so test both at the same time.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    @rcm,

    But wouldn't the macro treat that extra data as being within the (new) used range, and only delete the (blank) rows and columns beyond it?

    Pete

  9. #9
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    RCM,

    I tried this, however the macro treats this as part of the range and doesn't delete the tested values. So Pete's statement is correct and is the problem I am running into to test jakobshavn's macro.

  10. #10
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: VBA Find last row and column that contain data and delete all blank rows and blank col

    I just found a way to mark an un-used row/column.

    In an empty worksheet, put 1 in both B2 and C3.
    Assign cell D4 the Name "qwerty" (D4 is outside UsedRange). Then:

    Please Login or Register  to view this content.
    Produces expected results. If you now delete column D (outside UsedRange), the second MsgBox throws an error, proving the column was, indeed, erased.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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