Cannot remove empty rows

[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

 

[lhkittle]

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

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

 

[GS]

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

 

[GS]

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

 

[Terry Pinnell]

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.

Thanks both. Hadn't realised I needed to SAVE the sheet before deletions
would be fully recognised.

 

[GS]

Thanks both. Hadn't realised I needed to SAVE the sheet before
deletions would be fully recognised.

You're welcome! Always happy to help...

--
Garry

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

 

[Terry Pinnell]

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.

Thanks both, and apologies for long delay in acknowledging.

 

[joeu2004]

Thanks both. Hadn't realised I needed to SAVE the sheet before
deletions would be fully recognised.

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.

 

[GS]

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.

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