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.

 

[SkipVought]

Hi,

Ooooo, this does not sound good to me: tables with empty rows is not good!

That being said, use COUNTA to count valus occurances in a row. If any row has ZERO value occurences, it is a candidate for hiding. Use the AutoFilter to hide.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

Is the entire row blank? Or just the cell in the column you want to display?

The first thing that comes to my mind is simply sorting, but not if you want to keep the blank rows in place. (Though I agree with Skip - blank rows in a table are not a good idea.)

[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.

 

[SHAWTY721]

It isn't a table it is just data that is sent to excel from the access database based on the parameters set by the user. I just need to find a way to hide the blank rows of the excel spreadsheet after I link the two spreadsheets together.

 

[AnotherHiggins]

Select all the data. (Depending on your version of Excel, you might do this by pressing Ctrl]+[A] once or clicking on A1 then pressing [Ctrl]+[Shift]+[End] - or however you want. Just make sure that all used rows and columns are selected)

Go to Data > Filter > Auto Filter

Select a column where the rows you want to hide are always blank

Single click on the arrow at the right side of the header for that column

At the very bottom of the drop-down, select Blanks

Select all cells in that column (except for the header record) all the way down to the end of the used rows

Right click on any of those selected cells

Select Hide > Entire Row

Go to Data > Filter > Auto Filter (to turn off AutoFilter)


[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.

 

[SHAWTY721]

Okay is there a way to do that programmatically after the spreadsheet populates with data. So I don't have to go in and do this for each user.

 

[AnotherHiggins]

turn on your macro recorder (Tools > Macro > Record New Macro) then go through the steps.

Observe the code that was generated ([Shift]+[F11]).

Post the code to forum707 for help cleaning it up.

[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.

 

[SHAWTY721]

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.

 

[SkipVought]

If you are talking about macros and doing stuff automatically, then this discussion ought to be taken to Forum707, where workbook and worksheet events can be thoroughly explored.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}