VBA for Autohiding empty rows

**slipleft** · 10-12-2011, 01:40 PM

I've got a spreadsheet using VBA to autohide blank rows. Code is working on one worksheet, and 'error 13' on two others: anyone interested in helping to resolve??

Not a competent VBA user and sourced the code from the web. So trying to debug has proved incredibly difficult as I am unsure of what the code means! (It also doesn't work too fast)

Also note that the rows are not 'blank' as they are conditionally pulling data from a root worksheet - naturally, some rows do not meet the criteria and look empty though are technically " " (if this is helpful)... I do like the way the current version runs when the worksheet is activated!

Please Login or Register  to view this content.

**davegugg** · 10-12-2011, 01:55 PM

You are probably recieving an error because you are trying to sum strings; excel can't calculate dave+gugg+89, the data has to all be numbers. Normally, you'd use the CountA() function to check the number of cells that aren't blank, but this won't work if you are using formulas. I believe you are going to have to loop through each cell in each row and check if there is no value. This is going to take longer to run:

Please Login or Register  to view this content.

I'd also like to add that storing a lone space in a cell (" ") is poor practice; there is no easy way to discern whether the cell is holding data or not. Better would be to use a blank ("").

**slipleft** · 10-12-2011, 02:12 PM

Great explanation. In fact I really only need to have the first column analyzed: if this column does not contain a number >>> the entire row is void and should be hidden. Would that make things easier?

Also, when i pasted the code in i received "Next without For" compile error...

Regarding the " ", was a typo and is in fact a "" blank.

**davegugg** · 10-12-2011, 02:45 PM

Ok, if you only need to check the first column, you can shorten it up nicely:

Please Login or Register  to view this content.

I've also edited my code in post 2 to fix the End If I missed, so that should work properly now if you should need to check across the entire row in the future.

**slipleft** · 10-12-2011, 03:13 PM

Code in Post 2 does the job nicely! Almost in disbelief after how many hours I've spent...

Code in post 4 needs definition for HiddenRow. Should I include a 'Dim HiddenRow As Integer' as in Post 2?

**davegugg** · 10-12-2011, 03:23 PM

That should probably be there. Although defining the variables isn't required in VBA, most people (me included) consider it a good practice. The reason VBA stopped you is because you have marked Option Explicit, which forces you to define all variables.