Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Deleting Empty Rows in Excel 2

Status
Not open for further replies.

MyFlight

Technical User
Feb 4, 2002
193
Help,
I have a Large workbook running multiple Macros to import, compare and generate reports.
The Problem is that after all is said and done I endup with a workseet that has about 5000 rows, when only maybe 1000 have data in them. The other 4000 are blank, I nned to remove these as they increase the size of the workbook exponentially. Since this worksheet has between 7 and 10 worksheets with the same problem.

Since I am deleting alout of rows I am also worried about the Macro slowing down too much.
I already have Application.ScreenUpdating = False


Any Ideas or suggestions.
 
Why the multiple Macros are creating empty rows ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Yes some of the Data imported includes blank rows.

any help will be appreciated.
 



Hi,

You can use your macro recorder to create some of the necesary code. What you record on one sheet can be executed on any or all fo your sheets. Post back when you have some.

Turn on the AutoFilter. Filter to display ONLY the empty rows. Select the ROWS containing the empty rows. Delete Rows.

Skip,

[glasses] [red][/red]
[tongue]
 
Whats the format of the data? If column A is empty are all of the other rows empty as well?

Is there an identifier for the last row?

What column range are we talking?

Assuming you aren't using all available columns and a blank column A implies a blank row, try the following:

Code:
Sub Macro1()
    Range("A2").EntireRow.Insert
    Range("A65536").Select
    Selection.End(xlUp).Select
    r = ActiveCell.Row
    MsgBox r
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1:A" & r).Formula = 1
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="="
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    Selection.AutoFilter
    Columns("A:A").EntireColumn.Delete
End Sub

If blank column A doesn't imply a blank row, change the formula in for the inserted column A to be equal to a CountA function based on the columns to the right and then filter on Field1 with the Criteria of equalling zero.

Normally, I'd do the above without VB but as it's a VB forum......

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?
 
Err, spooky.

Question for Skip: Can you use VB to get past the "CurrentRegion" limitations of autofilter?
(The reason why I included a column of 1's at the start of the block in my code which I was typing when you posted your reply).

D

 



Having a column of 1's to " get past the "CurrentRegion" limitations of autofilter" is a CRUTCH for a poorly constructed table. What if the table is 256 columns wide -- no place for a column of 1's.

Tables should NEVER contain empty columns or rows.

However, in this particular case, referencing the ENTIRE COLUMN for the column range, and THEN applying the AutoFilter, produces the desired effect.

Skip,

[glasses] [red][/red]
[tongue]
 
CurrentRegion" limitations of autofilter"???
If you explicitly select the range before data,filter,autofilter then there are no such limitations.

Range("A1").Select
Selection.AutoFilter

Forces Excel to guess what range you want to filter. And it guesses the CurrentRegion.

Range("A2:AB435").Select
Selection.AutoFilter
Would overcome the 'limitation'. Although
Range("A2:AB435").AutoFilter
would be more efficient.

Without code the same solutions apply. I often come across reports with multiple rows containing the headings and blank rows within them. I select a cell from the last of the header row, CTRL-Shft-End to extend selection to the last cell and then apply autofilter.


Gavin
 
Skip & Gavin, thanks to both for the pointers.

Skip - Whether I could enter the additional column was the reason for the question I asked about the column range BUT I have to admit I have a lot of crutches to get a job done.
You're the pro, I'm the student - just asking questions to improve for the future.

Thanks for the advice to both.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top