Code to Hide Rows based on criteria in linked cell

fin_analyst

New Member
Joined
Dec 4, 2013
Messages
2
I have read about 50 threads and cannot seem to get a macro code to work and I am not well versed in VBA so I have been trying to cut and paste certain codes to get to one that works without success.

My workbook contains an "Expense" tab and a "RPT" tab. The RPT! tab pulls data from a separate database. The goal is to have specific lines in the Expense tab hidden based on cell F3 on the expense tab which contains a formula (=right(RPT!A4,7). I tried to link the macro to the RPT!A4 but when the sheet updates from the separate database the macro errors. I should also mention that the call RPT!A4 and Expense!F3 is a number stored as text.

I learned that I can't use Worksheet_Change(ByVal Target As Range) when I am trying to run a macro triggered by a formulated cell so I am trying to use Worksheet_Calculate() but still nothing happens or it errors. I want it to run every time the number/text in Expense!F3 changes.

This is the current code I have:

Private Sub Worksheet_Calculate()

If Itersect(Target, Range("A4")) Is Nothing Then Exit Sub

If Target.Address = ("A4") And Target.Value <> "0990900" Then
Sheets("Expense").Rows("22:41,549:768").EntireRow.Hidden = True
ElseIf Target.Address = ("A4") And Target.Value = "0990900" Then
Sheets("Expense").Rows("22:41,549:768").EntireRow.Hidden = False
ElseIf Target.Address = ("A4") And Target.Value <> "6990905" Then
Sheets("Expense").Rows("46:270,372:402,813:3287,4404:4744").EntireRow.Hidden = True
ElseIf Target.Address = ("A4") And Target.Value = "6990905" Then
Sheets("Expense").Rows("46:270,372:402,813:3287,4404:4744").EntireRow.Hidden = False
End If

End Sub

Basically I want certain rows to not be visible unless the exact number/text is triggered. I have been working on this for days. Can someone please help me???
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top