• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hide rows but cell has formula

J

joostlaane

New Member
hi,


i am looking for a macro/formula that if the row is empty that it hides itself, but the row has to appear again if the formula that is in that row gives a result.


And is it possible to have a date changed in a cell if you have changed something in the workbook. So if you don't change any information on the workbook the old date (of the previous changes will stay)
 
For your first question, this should work. Right-click on sheet tab, view code, paste this in.

[pre]
Code: 
Private Sub Worksheet_Calculate()
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 To LastRow
'which column are we checking for values?
Cells(i, "A").EntireRow.Hidden = (Cells(i, "A").Value = "")
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
For the second question, you can use another event macro, but this one needs to go into the ThisWorkbook module of the VBE.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim MyRange As Range
'Define the cell where you want the time stamp
Set MyRange = Worksheets("Sheet1").Range("A1")

Application.EnableEvents = False
MyRange.Value = Now
Application.EnableEvents = True
End Sub
[/pre]
Alternatively, you could just check the "last modified on" date within file properties.
 
Thank you very much Luke M,

but I am wondering is it possible that it doesn't work if you have already a formula in the cell cuz on a try sheet it works great but on a page where there are formula's involved he doesn't want to work :s


thanks!
 
Can you be clearer as to which one isn't working? If the latter one, I was working with the assumption that even if it's just a formula that changed, something must be causing that change (ie, a user input)
 
the first formula isn't working, in my cells there are some IF functions, so sometimes there are some date in it and sometimes not. but the macro that you have send doesn't if in the formula is a funciton in it...

cheers
 
I am unable to duplicate your problem. Since the macro is triggered whenever the sheet recalculates, it shouldn't matter if it's a formula or not. Are the formulas actually in col A, or are they somewhere else?
 
Hi, joostlane, Luke M!


Sorry to interrupt you. I've downloaded the file and I noticed that the code under the Worksheet_Calculate event perform this check:

... (Cells(i, "B").Value = "")

So it's checking against a zero length value string, and in some formulas (E38 for example) you have a " " (single space, length 1, value string). Maybe is this what happens in other places? I haven't analyzed all formulas in columns B/D, but despite of this I found you're looping for check performing from row 4 thru row LastRow, which is defined as last non-empty cell in column A. But column A is empty, try changing it to column B.


Hope it helps.


Regards!
 
i have just changed all the formulas to "" at the end, but it still won't work! but thank you for this very observant remark SirJB7!
 
Hi, joostlaane!

You're welcome. Tried to changing LastRow definition from A to B? That's the major point, I think.

Regards!
 
Hi ,


To add to what SirJB7 has mentioned , change the statement within the FOR loop , by incorporating a TRIM function as follows :


Trim(Cells(i, "B").Value) = ""


Narayan
 
hi guys,


thank you very much the following code works brilliant:


Private Sub Worksheet_Calculate()

Dim LastRow As Integer

LastRow = Range("B65536").End(xlUp).Row

Application.ScreenUpdating = False

Application.EnableEvents = False


For i = 1 To LastRow

'which column are we checking for values?

Cells(i, "B").EntireRow.Hidden = Trim(Cells(i, "B").Value) = ""


Next

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub


but is it possible that you put a delay on it, e.g. that he only hides the lines if you push a button once ur ready with the sheet and only then runs the macro? because he runs it now every time you activate a box, and it takes forever...


thanks!
 
Hi, joostlaane!


Insert a button (Programmer, Insert, ActiveX controls, Command button), right click and assign properties as name, caption, backcolor, font, ... right click View code, and then move all the code in the Worksheet_Calculate event to the button click event.


Regards!
 
You must log in or register to reply here.
Back
Top