hide rows based on Column A value

E

excelnooby

New Member
Joined
Jun 30, 2009
Messages
10
Hi all,

Let me simplify my example instead of giving you all the full background on why i need to perform this function.

Basically, I have a range of cells "a1:a20", with numbers entered in each cell in the range, say 1, 2 or 3. I want to be able to write a macro to assign to three different buttons that will allow me to hide entire rows depending on the number entered in that range. for instance, click a button to hide all rows with the number '1' entered in column a, click a button to hide all rows with the number "2" entered in column a, etc.

thanking you in advance

Nooby
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
here is a macro for hiding rows with 1, then you can create the button and point to this macro
Code: 
Sub button_hiderows()
Dim i As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Cells(i, 1) = 1 Then
        Rows(i).EntireRow.Hidden = True
    End If
Next i
End Sub

HTH
 
Upvote 0
If you don't know how many columns it will be (you said 20...) but you do know that there won't be blanks in the first row, then you could use this to hide if row 1 = 2:

Code: 
Dim i As Integer
i = 1
While Cells(1, i) <> ""
    If Cells(1, i) = 2 Then       ' the 2 is the criteria
        Cells(1, i).EntireColumn.Hidden = True
    End If
    i = i + 1
Wend

If you do know how many columns there will be and it is 20, you could use this:

Code: 
For Each cell In Range(Cells(1, 1), Cells(1, 20))  'the 20 is the number of columns
    If cell.Value = 2 Then
        cell.EntireColumn.Hidden = True
    End If
Next

Edit: I'm an idiot! My thing is for columns, not rows. I won't bother fixing it, bc my answer was late anyhow :)
 
Last edited:
Upvote 0
hey guys,

thanks so much...i actually used a combination of these solutions. I really appreciate the help!!!!
 
Upvote 0
If you are not using AutoFilter elsewhere on your sheet, then you might be able to use something like this for each of your buttons.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdHide1_Click()<br>    Columns("A").AutoFilter Field:=1, Criteria1:="<>1"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

sparky2205
  • Solved
Loop increment help required
Replies
3
Views
286
rlv01
R
N
  • Question
Toggle code to hide and unhide columns based on text in a row
Replies
1
Views
347
Cubist
Cubist
C
  • Question
VBA hide/unhide
Replies
2
Views
426
Cancyy
C
J
  • Question
Hide header row if range of cells in column is 0
2
Replies
17
Views
730
jamesyvoi
J
N
  • Question
Hiding/showing rows between two values
Replies
1
Views
302
Joe4
Joe4

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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