Cannot remove empty rows

  • Thread starter Thread starter Terry Pinnell
  • Start date Start date
T

Terry Pinnell

This is really puzzling me. I had 109 rows in the sheet and I deleted all
but the first 20. (I also tried Cut and 'Remove content', in various
sequences and several times.) But Excel still insists on regarding the
sheet as having 109 rows. How do I fix that please?

(For the curious, I'm demonstrating an apparent bug in my Snagit capture
program and want to scroll a small window.)

https://dl.dropboxusercontent.com/u/4019461/Excel-CannotRemoveRows.jpg
 
This is really puzzling me. I had 109 rows in the sheet and I deleted all

but the first 20. (I also tried Cut and 'Remove content', in various

sequences and several times.) But Excel still insists on regarding the

sheet as having 109 rows. How do I fix that please?



(For the curious, I'm demonstrating an apparent bug in my Snagit capture

program and want to scroll a small window.)



https://dl.dropboxusercontent.com/u/4019461/Excel-CannotRemoveRows.jpg
Click to expand...

Try this if you already have not.

Select all the rows from 21 on down, on the Home tab > Delete (icon down arrow) > Delete rows.

Select all the columns from C on to the right Home tab > Delete > Delete columns.

Save workbook and close > re open and see if that did the trick.

Regards,
Howard
 
Excel regards the UsedRange as it was until the workbook is saved after
deleting the unwanted cols/rows. You can find the last cell in the
'official' used range using the keyboard combo *Ctrl+End* if you're in
doubt as to the number of cols/rows Excel recognizes as the UsedRange.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Save workbook and close > re open and see if that did the trick

Closing is not required. See my reply for an easy way to test for what
Excel regards as the 'official' used range...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
Excel regards the UsedRange as it was until the workbook is saved after
deleting the unwanted cols/rows. You can find the last cell in the
'official' used range using the keyboard combo *Ctrl+End* if you're in
doubt as to the number of cols/rows Excel recognizes as the UsedRange.
Click to expand...

Thanks both. Hadn't realised I needed to SAVE the sheet before deletions
would be fully recognised.
 
GS said:
Excel regards the UsedRange as it was until the workbook is saved after
deleting the unwanted cols/rows. You can find the last cell in the
'official' used range using the keyboard combo *Ctrl+End* if you're in
doubt as to the number of cols/rows Excel recognizes as the UsedRange.
Click to expand...

Thanks both, and apologies for long delay in acknowledging.
 
Terry Pinnell said:
Thanks both. Hadn't realised I needed to SAVE the sheet before
deletions would be fully recognised.
Click to expand...

Yes, that is correct if you rely on keyboard actions alone, AFAIK.

However, GS's explanation regarding UsedRange per se is incorrect.

In a macro, just the reference to ActiveSheet.UsedRange corrects Excel's
knowledge of the __truly__ last-used cell in the worksheet without the need
to save the workbook first.

For example:
MsgBox ActiveSheet.UsedRange.Address
or
Dim r as Range
Set r = ActiveSheet.UsedRange
or
ActiveSheet.UsedRange.Select

In contrast, the keyboard combination ctrl+End is implemented as
ActiveCell.SpecialCells(xlLastCell).Select. That does have the problem that
you are seeing.
 
Terry Pinnell said:
Yes, that is correct if you rely on keyboard actions alone, AFAIK.

However, GS's explanation regarding UsedRange per se is incorrect.

In a macro, just the reference to ActiveSheet.UsedRange corrects
Excel's knowledge of the __truly__ last-used cell in the worksheet
without the need to save the workbook first.

For example:
MsgBox ActiveSheet.UsedRange.Address
or
Dim r as Range
Set r = ActiveSheet.UsedRange
or
ActiveSheet.UsedRange.Select

In contrast, the keyboard combination ctrl+End is implemented as
ActiveCell.SpecialCells(xlLastCell).Select. That does have the
problem that you are seeing.
Click to expand...

Yes, you are correct. Thanks for catching that!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top