×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Deleting Columns and Making Sure The Used Range Shrinks Accordingly
2

Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Deleting Columns and Making Sure The Used Range Shrinks Accordingly

(OP)
Good afternoon, I apologise for the clumsy title. Basically, over the years I've found that the only way to reduce the number of used Columns (or Rows) after having 'Deleted' them is to save the workbook. If I don't and then just key Ctrl+End I end up at the bottom of the previously used range - somewhere among the 'unused' Columns (or Rows).

Accordingly I have incorporated this into some code as I'm particularly keen on shrinking the size of a workbook having bothered to try to tidy it up:

CODE

''    'delete columns from column K rightward
    .Range(.Columns(11), .Columns(LastCol)).Delete
    ActiveWorkbook.Save 

But I just wonder if there is any other way to ensure that the last cell is actually in the last used Column & Row?

Many thanks,
D€$

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Hi, you do realize that some “deletes” simply CLEAR the contents of cells, but leaves DATA. In order to actually delete all data, the process must also shift cells.

The UsedRange property includes cells with data more that just the values in cells.

If you indeed delete ALL the data, you need not Save the workbook to observe the results.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

(OP)
Thanks Skip, I'm just going from my experience of doing this manually on a sheet by Right Clicking and choosing Delete... Entire Column. I'll make sure that 'Shift Left' is included in my code and see what happens.

Many thanks,
D€$

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Quote (Skip)

some “deletes” simply CLEAR the contents of cells, but leaves DATA

Skip, could you elaborate on this a little?
If the cells are CLEARED, but DATA still is there, where does the DATA reside?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

(OP)
Hi guys, it's as though the 'used range' is still the same and Ctrl+End will go to the previous last cell.

I've just tested this again and even including

CODE

Shift:=xlToLeft 
still means that Ctrl+End still goes to previous last cell; if I then allow the workbook to Save then Ctrl+End goes to the last row in Column "J" (10).

Many thanks,
D€$

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Formatting is data.

If you have a cell way out in left field with some kind of formatting that is different than the formatting surrounding it, that is interpreted as data and that cell is included in the UsedRange of the sheet.

I just tested this by creating a new sheet.
1. I entered a value in A1
2. In the Immediate window

CODE

Debug.Print Activesheet.UsedRange.Rows.Count 
And I get 1

3. Go to C5 and change the font size or font type

4. Run the debug and get 5

Simply hitting the DELETE key only clears cells of values. You must use
right-click > DELETE > SHIFT CELLS....
in order to eleminate all data from the selected range.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

(OP)
Yes, just a bit of b*tch that unless I save after I've (say) deleted Columns K-AQ then Ctrl+End will take me to the last row in AQ.

UNLESS (Shoot me, as I'm not used to using the Immediate Window so I just tested with this) I run this little bit of code and afterwards Ctrl+End takes me to the last row in Column "J".

CODE

Sub MyDebug()
Dim MD As Long
MD = ActiveSheet.UsedRange.Columns.Count
End Sub 

That's rather interesting. So if I incorporate this into my code, thus:

CODE

Sub MyDebug()
Dim MD As Long
Dim LastCol As Long

    LastCol = Range("A1").CurrentRegion.Columns.Count
    'delete columns from column K rightward
    Range(Columns(11), Columns(LastCol)).Delete
    
MD = ActiveSheet.UsedRange.Columns.Count

End Sub 

Ctrl+End takes me to the last row in Column "J".

I'll take that! :)

Many thanks,
D€$

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

I've been following this thread with interest, because the behaviour of Ctrl-End has often annoyed me.  I have just done a few tests using my Excel-2010, and it seems to me that
    Activesheet.UsedRange.Rows.Count
takes the .count in its title extremely literally.  The number it returns is NOT that of the last used row, but is the number of rows that are used (except in the case when that number would be zero, when it returns 1).

So, Ѐ$, before you move on to your next problem I suggest you test out your strange fix on a worksheet that has a few fully-null rows and/or columns within its UsedRange.  And please let us know if you find a robust solution to this problem.

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

2
on a new sheet enter alpha-numeric in C3:E5...

ActiveSheet.UsedRange.Select

...will select C3:E5.

ActiveSheet.UsedRange.Rows.count

...will return 3.

If you change the font size in empty cell B2 and change the font type in empty cell F6...

ActiveSheet.UsedRange.Select

...will select B2:F6.

ActiveSheet.UsedRange.Rows.count

...will return 5.

I never have empty rows above my data or empty columns to the left of my data, so in practice I only DELETE SHIFT... to the right of and below my data to remove extraneous data.

Another thing to be aware of for the used range is...

CODE

With ActiveSheet.UsedRange
   Debug.Print “First row: “ & .Row
   Debug.Print “Last row: “ & .Row + .Rows.Count - 1
   Debug.Print “First column: “ & .Column
   Debug.Print “Last column: “ & .Column + .Columns.Count - 1
   Debug.Print “Total rows: “ & .Rows.Count
   Debug.Print “Total columns: “ & .Columns.Count
End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Thanks, Skip.

Inspired by your information, I have just run a simple experiment (on Excel-2010).  I started a new spreadsheet.  I put an "x" in cell B11, then I changed the font in cell H6.  At this stage, the Ctrl-End command moved the cursor to cell H11 (as one would hope).  I then deleted column D, after which the Ctrl-End command still moved the cursor to cell H11 ("wrongly" but expectedly).

Next I fired up VBA, and in the Immediate Window I ran the command
    ? ActiveSheet.UsedRange.Cells.Count
This gave me the expected answer of 36.  When I returned to the worksheet, the Ctrl-End command had changed its mind and now moved the cursor to the correct cell G11.

So it is beginning to look like one way to correct the "value" of Ctrl-End in the spreadsheet environment is to invoke the UsedRange "parameter" in the VBA environment.  This is totally consistent with the workaround that DES described above.  However there might be some UsedRange "contexts" in which this does not work.

It's a pity that we have to resort to such ruses to get correct the behaviour of Ctrl-End.

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

I frankly have never used Ctrl-End. Never found a need.

Have used End-DownArrow or some other arrow.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

I often use it when developing a complicated spreadsheet.  I tend to put extraneous bits of calculation (such as cross-checks, numerical doodles, trial formulae, etc) on far-flung parts of the worksheet well clear of what will eventually be the final product.  So I have to tidy things up at the end, and one of the checks that I haven't left something where it shouldn't be is to see where Ctrl-End lands me.

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Ah. I typically use a sheet for trial stuff like that, that I delete when all is done.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

(OP)
Thanks guys, it was way back in 1999 that I discovered that if I saved after deleteing rows below or columns to the right (probably also used for calculation checks too!) that would actually reduce the used range, and the file size, - before I started dipping my toe into VBA. I still have a look at Ctrl+End if I encounter what I believe should be a small workbook which appears to several MB so that I can delete the extra rows/columns.

Many thanks,
D€$

RE: Deleting Columns and Making Sure The Used Range Shrinks Accordingly

Thanks, Skip, for your explanations of this one. Very interesting. I think I've had times in the past I wondered why in the world Excel thought something was the end when it was empty. Don't remember the specifics, but somewhat remember the event. Very interesting indeed.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close