Deleting Columns and Making Sure The Used Range Shrinks Accordingly 2

[PWD]

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:

''    '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€$

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

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€$

 

[Andrzejek]

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.

 

[PWD]

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

 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€$

 

[SkipVought]

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

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".

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:

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€$

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

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.

 

[SkipVought]

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...

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

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.

 

[SkipVought]

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

Have used End-DownArrow or some other arrow.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

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.

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

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€$

 

[kjv1611]

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