Can't delete empty but active cells

[Peter Chatterton]

I have a work sheet with 65,536 rows
only the first 12,000 of which have data,
but I can't figure out how to delete the empty but active rows.

I could do a reverse Find from the end and then delete the trailing Range,
but hoped there might be an easier way.

I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's
Reference,
(www.wrox.com/WileyCDA/WroxTitle/productCd-0764543717,descCd-download_code.html)
might work (going by the title), but it doesn't.

Sub DeleteEmptyRows()
Dim rngRow As Range
For Each rngRow In ActiveSheet.UsedRange.Rows
If WorksheetFunction.CountA(rngRow) = 0 Then
rngRow.EntireRow.Delete
End If
Next rngRow
End Sub


Hope you can help,
Peter

 

[AKphidelt]

Wait, are you saying you want only the 12,000 rows showing data to show and
delete the remaining rows? Or is there formulas or something that you are
trying to get rid of below the 12,000 rows?

Because I don't think it's possible to actually delete the blank rows. You
can hide them.

 

[JLatham]

Alternatively (from what AKphidelt wrote), if you have had data in those
high-numbered rows, take at look at this page:
http://www.contextures.com/xlfaqApp.html#Unused

But if they've never actually been part of the 'UsedRange', then hiding them
is pretty much the only solution.

 

[FSt1]

hi,
I may be misunderstanding but as i read your post, you want to delete the
empty rows below your data?!?!?
not possible.
excel is fixed at 255 columns wide and 65536 rows high. and that is etched
in stone by the excel gods at microsoft.
you can hide them so that you can't see them but they will always be there.
Sorry.
to hide rows....
Select the first row below your data. shift+end+down. format>row>hide
to unhide rows.....
select the sheet. format>row>unhide
regards
FSt1

 

[Peter Chatterton]

The spread sheet was downloaded from a website using a non-Excel
application.
I'm not sure who did the conversion, but i think he just checked a box
that asked for the format.

What does hiding the rows mean?

Thanks,
Peter

 

[Peter Chatterton]

Okay, so I just want to hide the rows;
but I want to do it in a macro using 2002 VBA.

Peter.

 

[AKphidelt]

Try something like this, not sure if it will read the cells correctly or
not... but try

Range("A1").End(xlDown).Offset(1,0).Activate
Range(ActiveCell,ActiveCell.End(xlDown)).EntireRow.Hide

Im not next to excel right now, so this is just off the top of my head.
Someone else probably has something better.

 

[Peter Chatterton]

Will you please explain what is wrong with Delete?
It's what I do all the time manually, and it shows
a reduced row count.

For some reason or other I can't duplicate it right now,
but maybe that's just Excel.

 

[Peter Chatterton]

Thanks for the suggestion AK, but Excel claims that the Hide metod
is something that only applies to UserForms and that seems to be
supported when i try and run it.

Peter