Hiding or Deleting Blank Rows on Excel Spreadsheet

[SHAWTY721]

I have this excel spreadsheet that is linked to another spreadsheet. I was wondering is there a way to programmatically hide or delete blank rows on the excel spreadsheet. So the spreadsheet will only show rows that contain values.

Okay is there a way to do this so automatically once a spreadsheet is created. I am asking this because there will be various spreadsheets created by various users and all will have different amounts of data. So is there a way to program some vba to do this each time a spreadsheet is created based on the template that has been created with the basic spreadsheet column layout and where columns should begin.

 

[AnotherHiggins]

First we need to figure out the process we'll use to delete empty rows.

The easiest - and fastest - way is to select all empty rows in a single column, then delete those rows. So - important question here:

Is there any column that will contain an empty cell:
[tab]* for every row you want deleted
[tab]* only for rows you want deleted

For example, Maybe column A is never blank unless the entire row is blank.

Do you have such a column?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

This will delete all rows where column A is blank:

Sub DeleteRows()
Range([A1], [A1].SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:="="
Range([A2], [A2].SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter
Range("A2").Select
End Sub

Now for the second part.

is there a way to do this so automatically once a spreadsheet is created

There'd be no point in running the code when a workbook is created - because at that point it contains no information and therefore no empty rows.

I'd suggest putting this code in your PERSONAL.xls file. This is an automatically-created workbook that will automatically open every time you start Excel.

That way you can run this macro whenever you need to. You can either assign a keyboard shortcut to fire the macro or create a custom toolbar and create a button that kicks off the macro when clicked.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.