VBA to hide blank rows on open(refresh)

[jw_]

Hi,

I have a spreadsheet that has cell references to another spreadsheet.
I am trying to write a macro script that when the document is opened (and refreshes data from the linked spreadsheet) it looks at the rows which are blank and hides these rows.

So far I have found the following code below which works great, however this macro re-runs everytime you navigate back to the worksheet, causing the screen to flicker for a few seconds.

Please Login or Register  to view this content.

I just want the script to run once.

I have tried replacing the top line of script with:

Please Login or Register  to view this content.

However I was then getting a debug error associated with line 3.

Can anybody offer any help?

Thanks in advance!


Josh

[solnajeff]

Hi

To be effective you need to have the line

Please Login or Register  to view this content.

at the beginning of your code, then it does not matter if it runs once or many times it is very fast and does not flicker.

Regards

Jeff

[jw_]

thanks for your help!! works a treat!

[DonkeyOte]

...I have found the following code below which works great, however this macro re-runs everytime you navigate back to the worksheet, causing the screen to flicker for a few seconds.

You could think about using a Static variable such that the code is invoked only once in any given "session", ie along the lines of:

Please Login or Register  to view this content.

given you're processing the row in it's entirety there's no need to process each cell in the multi column range, one cell per row should suffice I think.

[jw_]

thankd for all your help guys.

i have another application for this script, however I am looking at using it over a range where some columns contain formulas.

These formulas can have a result of "".

I want the script to recognise the "" as being a true 'blank' cell value.

I have tried using a Len() function, but have not had any luck getting it right.

Please Login or Register  to view this content.

Is there an easier way to acheive this?

Thanks

[DonkeyOte]

COUNTBLANK, unlike COUNTA, equates Null to Blank, on which basis perhaps the below will work for you ?

Please Login or Register  to view this content.

Again as before the above utilises a Static variable such that the code is invoked only once in any given "session" - alter/remove as required.

[jw_]

Fantastic!! thanks for your help, greatly appreciated!!

[goPatsgoSox]

I used this code and changed the range and it works great for my application but I have some cells that I need to hide that contain/display "#N/A"...how can I edit this code to also hide this?

Private Sub Worksheet_Activate()
Dim rng As Range, cell As Range
Static bCount As Byte
If bCount Then Exit Sub
Application.ScreenUpdating = False
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A7:A90"))
For Each cell In rng.Columns(1).Cells
If Application.CountBlank(Intersect(cell.EntireRow, rng)) = rng.Columns.Count Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
bCount = bCount + 1
End Sub

[DonkeyOte]

goPatsoSox, welcome to the Board, however, please note that:

Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

EDIT: I see you have done this already

and also


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


Please take the time to read the Forum Rules and adhere to them thereafter.