Hiding rows with a macro

A

Aidan

Board Regular
Joined
May 14, 2004
Messages
121
Hi there

Working my way through developing a spreadsheet for a charity that is easy for them to use and gives them graphical information for their current financial year.

(I had another thread entitled dont want to go back past a certain date but it didnt get any responses as probably didnt explain myself properly).

Anyway I have a sheet that shows the previous twelve months data and if that twelve months go back past Sep 16 then the values are shown as blank (October is the start of their financial year). I only then want to chart rows that have values and I tried this macro and it worked.....but when I changed the date it didnt bring back the hidden rows!!! (I got this macro from a website and changed the range to the range of my data) Any suggestions:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("c40:c51")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True

Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Update - this works to a point - the point being that to update you have to hit return on one of the cells (c40:c51) to make it work. It wont automatically work.

Is there a reason for this?
 
Upvote 0
Update - this works to a point - the point being that to update you have to hit return on one of the cells (c40:c51) to make it work. It wont automatically work.
Click to expand...
Work automatically based on what, exactly?

A "Worksheet_Change" event procedure is a macro that runs automatically, upon the event of a cell being physically updated.
There are other event procedures which are triggered by other events. Which one you need to use depends on when exactly you want this macro to run.
What "event" should trigger the macro to run?
 
Upvote 0
Aidan said:
Update - this works to a point - the point being that to update you have to hit return on one of the cells (c40:c51) to make it work. It wont automatically work.

Is there a reason for this?
Click to expand...

Worksheet_Change event does not work with formula changes if this is what you are trying to do?

Dave
 
Upvote 0
Joe4 said:
Work automatically based on what, exactly?

A "Worksheet_Change" event procedure is a macro that runs automatically, upon the event of a cell being physically updated.
There are other event procedures which are triggered by other events. Which one you need to use depends on when exactly you want this macro to run.
What "event" should trigger the macro to run?
Click to expand...

dmt32 said:
Worksheet_Change event does not work with formula changes if this is what you are trying to do?

Dave
Click to expand...

Hi and thanks for replying.

If i change the date in another worksheet it will update the figures In c40:c51 and for those that have nothing in them I want to hide them and for it to be done automatically.

In c40:c51 here are formulas that are date dependant. Hope that helps.
 
Upvote 0
Worksheet_Change only handles manual changes done to that sheet.
If the value you are changing is on another sheet, you would need to put the Worksheet_Change event procedure on that sheet, and look for the change to the cell holding the date, then run the macro against the data on the other sheet.

Something like this:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xRg As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
'   Check to see if cell A1 updated (if not, exit sub)
    If Target.Address <> "$A$1" Then
        Exit Sub
    End If
    
'   Designate which sheet data is on
    Set ws = Sheets("Sheet3")
    
'   Run code to hide cells
    For Each xRg In ws.Range("c40:c51")
        If xRg.Value = "" Then
            xRg.EntireRow.Hidden = True
        Else
            xRg.EntireRow.Hidden = False
        End If
    Next xRg
    
    Application.ScreenUpdating = True
    
End Sub
So you will need to move this code to the Sheet module where you are physically updating your date, and then change the cell and worksheet reference I added to the code to match your situation.
 
Upvote 0
Hi Joe

Here is my version of the code you used:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

If Target.Address <> "$e$3" Then
Exit Sub
End If

Set ws = Sheets("Workings")

For Each xRg In ws.Range("c40:c51")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg

Application.ScreenUpdating = True

End Sub

Where e3 is the cell that contains the date I change and "Workings" is the name I have given to the worksheet - when you look at the LHS when in VBA is has Sheet3(Workings).

I have placed this code in Sheet2(TopSheet) as this is where the date is.

When I change the date in TopSheet it doesnt blank the rows in c40:c51 where there is a blank

Any ideas or have I done something daft?

Cheers


Aidan
 
Upvote 0
When I change the date in TopSheet it doesnt blank the rows in c40:c51 where there is a blank
Click to expand...
Two things to check:

1. Make sure that events are enabled. You can run this one line code to make sure that they are:
Code: 
Sub EnableEvents()
    Application.EnableEvents = True
End Sub

2. Make sure that your cells in C40:C51 really are returning blanks.
What exactly are the formulas you have in there? Can you post it?
 
Upvote 0
Joe4 said:
Two things to check:

1. Make sure that events are enabled. You can run this one line code to make sure that they are:
Code: 
Sub EnableEvents()
    Application.EnableEvents = True
End Sub

2. Make sure that your cells in C40:C51 really are returning blanks.
What exactly are the formulas you have in there? Can you post it?
Click to expand...

Hi Joe

Thanks again.

In terms of 1 where would I put that?

And 2: - =IF($B$47>$H$37,LOOKUP($B$47,LetTotDate,'Letters Sheet'!C2:C49),"") - Currently C48:c51 are blank

The macro I used earlier worked manually (opening post) so wonder if it is how I have referenced the Sheet or whether events are enabled.
 
Last edited:
Upvote 0
Just place that VBA code in any module in your workbook, and then run it.
That will make sure that your events are enabled.
Then try again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
hide xrange and protect cells
Replies
2
Views
172
Peter_SSs
Peter_SSs
V
  • Question
VBA Command to create new worksheets with dates as worksheets name based on certain conditions
Replies
1
Views
140
Volrin
V
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
195
mumps
mumps
R
  • Question
VBA Progress bar
Replies
4
Views
746
Rhemo
R
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
226
birdman15
B

Forum statistics

Threads
1,214,636
Messages
6,120,664
Members
448,976
Latest member
sweeberry

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top