A macro to hide rows

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

All

A simple one but my brain hurts....

I have a worksheet, col B has a list of 4 distince numbers.
All I want is a simple way of hiding the other 3 when the user selects
their number.

eg data is in rows 4 and below. Each row in col B has 720,730,740,750
as an identifier.

All I want is that if you put 720 in A1 it then shows only rows with
720 in A1.

I would like this to be 'automatic' ie no filters etc

Each row is 30 odd columns wide so a macro would be good as each col
has a formular in it.

Thanks

Matthew
 
All

A simple one but my brain hurts....

I have a worksheet, col B has a list of 4 distince numbers.
All I want is a simple way of hiding the other 3 when the user selects
their number.

eg data is in rows 4 and below. Each row in col B has 720,730,740,750
as an identifier.

All I want is that if you put 720 in A1 it then shows only rows with
720 in A1.

I would like this to be 'automatic' ie no filters etc

Each row is 30 odd columns wide so a macro would be good as each col
has a formular in it.

Thanks

Matthew
Click to expand...

Put the folloowing evennt macro in the worksheet.code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Integer
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
v = Target.Value
n = Cells(Rows.Count, "B").End(xlUp).Row
Set rr = Range("B4:B" & n)
For Each r In rr
If r.Value = v Then
Else
r.EntireRow.Hidden = True
End If
Next
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
All

A simple one but my brain hurts....

I have a worksheet, col B has a list of 4 distince numbers.
All I want is a simple way of hiding the other 3 when the user selects
their number.

eg data is in rows 4 and below. Each row in col B has 720,730,740,750
as an identifier.

All I want is that if you put 720 in A1 it then shows only rows with
720 in A1.

I would like this to be 'automatic' ie no filters etc

Each row is 30 odd columns wide so a macro would be good as each col
has a formular in it.

Thanks

Matthew
Click to expand...

Even tho you said NO filters maybe you will like the speed of the
filter withOUT indicator "visibledropdown=false"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("a1").Address Then Exit Sub
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("b1:b1" & lr).AutoFilter Field:=1, _
Criteria1:=Target, Visibledropdown:=False
End Sub
Don Guillett Excel MVP
 
Even tho you said NO filters maybe you will like the speed of the
filter withOUT indicator "visibledropdown=false"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("a1").Address Then Exit Sub
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("b1:b1" & lr).AutoFilter Field:=1, _
Criteria1:=Target, Visibledropdown:=False
End Sub
Don Guillett Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Both work a treat.

One thing I missed out is that i would need it to change each time a
new number is dropped into A1. currently both sollutions hide to the
point there is nothing left to see....

I'm guessing an extra line somewhere to unhide ?

Regards

Matthew
 
Both work a treat.

One thing I missed out is that i would need it to change each time a
new number is dropped into A1. currently both sollutions hide to the
point there is nothing left to see....

I'm guessing an extra line somewhere to unhide ?

Regards

Matthew- Hide quoted text -

- Show quoted text -
Click to expand...

Send me your file (e-mail address removed)
 
Back
Top